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
|
Similar Threads
