This walks through the code and displays the filtered items, then jumps back
to line one of the code, retraces the original steps and when it hits the
autofilter again it give me an error that AutoFilter failed. I can't figure
out why it won't go to the next step and copy what it had filtered
originally. Can somebody explain what might be missing or what should be
omitted from this code? Appreciate the help.

Private Sub ListBox1_Click()
Dim ws As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim lr4, lc4, mCnt, cnt As Long
lr = Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
lr4 = Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row
lc4 = Sheets("Sheet2").UsedRange.Columns.Count + 1
Set ws = Worksheets("Sheet1")
Set rng = ws.Range("B26:AD" & lr)
myVar4 = UserForm1.ListBox1.Value
Sheets("Sheet1").Range("A25") = ListBox1.Value
For i = 0 To UserForm1.ListBox1.ListCount - 1
If UserForm1.ListBox1.Selected(i) Then
'With Application
'.ScreenUpdating = False
'.EnableEvents = False
'End With
ws.AutoFilterMode = False
cRng = Sheets("Sheet1").Range("A25").Value
rng.AutoFilter Field:=1, Criteria1:=cRng, VisibleDropDown:=False
ws.AutoFilter.Range.Copy
Sheets("Sheet2").Range("B" & lr + 1).PasteSpecial Paste:=xlValues
ws.AutoFilterMode = False
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End If
Next

try adding the following line of code
rng.AutoFilterMode = False


"JLGWhiz" wrote:

This walks through the code and displays the filtered items, then jumps back
to line one of the code, retraces the original steps and when it hits the
autofilter again it give me an error that AutoFilter failed. I can't figure
out why it won't go to the next step and copy what it had filtered
originally. Can somebody explain what might be missing or what should be
omitted from this code? Appreciate the help.

Private Sub ListBox1_Click()
Dim ws As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim lr4, lc4, mCnt, cnt As Long
lr = Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
lr4 = Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row
lc4 = Sheets("Sheet2").UsedRange.Columns.Count + 1
Set ws = Worksheets("Sheet1")
Set rng = ws.Range("B26:AD" & lr)
myVar4 = UserForm1.ListBox1.Value
Sheets("Sheet1").Range("A25") = ListBox1.Value
For i = 0 To UserForm1.ListBox1.ListCount - 1
If UserForm1.ListBox1.Selected(i) Then
'With Application
'.ScreenUpdating = False
'.EnableEvents = False
'End With
ws.AutoFilterMode = False
cRng = Sheets("Sheet1").Range("A25").Value
rng.AutoFilter Field:=1, Criteria1:=cRng, VisibleDropDown:=False
ws.AutoFilter.Range.Copy
Sheets("Sheet2").Range("B" & lr + 1).PasteSpecial Paste:=xlValues
ws.AutoFilterMode = False
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End If
Next

Thanks for the suggestion, Joel, but that didn't help. If expects the
worksheet reference there. That is what has me baffled. I can't find
anything in the code that should cause the error. I thought it might be that
I had some empty columns referenced in the filter range but after I changed
that, it still errors out. It is probably something really simple that I am
overlooking. I never use autofilter in my own code, so this is new ground
for me. I hate to tell the user that I can't fix this so it can be
integrated into some other code, but it looks like that's the case.

"Joel" wrote:


try adding the following line of code
rng.AutoFilterMode = False


"JLGWhiz" wrote:

This walks through the code and displays the filtered items, then jumps back
to line one of the code, retraces the original steps and when it hits the
autofilter again it give me an error that AutoFilter failed. I can't figure
out why it won't go to the next step and copy what it had filtered
originally. Can somebody explain what might be missing or what should be
omitted from this code? Appreciate the help.

Private Sub ListBox1_Click()
Dim ws As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim lr4, lc4, mCnt, cnt As Long
lr = Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
lr4 = Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row
lc4 = Sheets("Sheet2").UsedRange.Columns.Count + 1
Set ws = Worksheets("Sheet1")
Set rng = ws.Range("B26:AD" & lr)
myVar4 = UserForm1.ListBox1.Value
Sheets("Sheet1").Range("A25") = ListBox1.Value
For i = 0 To UserForm1.ListBox1.ListCount - 1
If UserForm1.ListBox1.Selected(i) Then
'With Application
'.ScreenUpdating = False
'.EnableEvents = False
'End With
ws.AutoFilterMode = False
cRng = Sheets("Sheet1").Range("A25").Value
rng.AutoFilter Field:=1, Criteria1:=cRng, VisibleDropDown:=False
ws.AutoFilter.Range.Copy
Sheets("Sheet2").Range("B" & lr + 1).PasteSpecial Paste:=xlValues
ws.AutoFilterMode = False
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End If
Next

the thing to be careful of with autofilter is the Field Number. This refers
to the X autofield on the worksheet. If you have autofields in columns C, D,
E then C is field 1, D is field 2, and E is field 3.

The end(xlup) will look at only the visible rows after filtering. I suspect
the first time you run the code different rows are visible then the 2nd time
you run the code which is creating the error. That is why I suggested that
you need to remove the filters before you run the code a second time.

"JLGWhiz" wrote:

Thanks for the suggestion, Joel, but that didn't help. If expects the
worksheet reference there. That is what has me baffled. I can't find
anything in the code that should cause the error. I thought it might be that
I had some empty columns referenced in the filter range but after I changed
that, it still errors out. It is probably something really simple that I am
overlooking. I never use autofilter in my own code, so this is new ground
for me. I hate to tell the user that I can't fix this so it can be
integrated into some other code, but it looks like that's the case.

"Joel" wrote:


try adding the following line of code
rng.AutoFilterMode = False


"JLGWhiz" wrote:

This walks through the code and displays the filtered items, then jumps back
to line one of the code, retraces the original steps and when it hits the
autofilter again it give me an error that AutoFilter failed. I can't figure
out why it won't go to the next step and copy what it had filtered
originally. Can somebody explain what might be missing or what should be
omitted from this code? Appreciate the help.

Private Sub ListBox1_Click()
Dim ws As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim lr4, lc4, mCnt, cnt As Long
lr = Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
lr4 = Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row
lc4 = Sheets("Sheet2").UsedRange.Columns.Count + 1
Set ws = Worksheets("Sheet1")
Set rng = ws.Range("B26:AD" & lr)
myVar4 = UserForm1.ListBox1.Value
Sheets("Sheet1").Range("A25") = ListBox1.Value
For i = 0 To UserForm1.ListBox1.ListCount - 1
If UserForm1.ListBox1.Selected(i) Then
'With Application
'.ScreenUpdating = False
'.EnableEvents = False
'End With
ws.AutoFilterMode = False
cRng = Sheets("Sheet1").Range("A25").Value
rng.AutoFilter Field:=1, Criteria1:=cRng, VisibleDropDown:=False
ws.AutoFilter.Range.Copy
Sheets("Sheet2").Range("B" & lr + 1).PasteSpecial Paste:=xlValues
ws.AutoFilterMode = False
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End If
Next