Runtime Error 1004

[Lizz45ie]

I have a user form with the following code and while the code is running
I received a RunTime Error 1004 - Method 'select' of object'_worksheet
failed. My code is set up to bring up a user form where I type a name
and press Get Data. The code should run through each worksheets and
autofilter the name that I typed in the user form. The code apears to
work - actually filters the last sheet and then the Runtime Error pops
up.

Private Sub cmdFilter_Click()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
sh.Select
Range("A2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=tbName
Next sh

Unload Me
Sheet1.Select
End Sub

Any help is appreciated.

 

[RPIJG]

try sheet1.activate

 

[Lizz45ie]

I'm not sure what you're suggesting; will you elaborate?

 

[RPIJG]

replace sheet1.select with sheet1.activat

 

[Guest]

You are best off to avoid the selects all together... They can be
problematic. Try this code...

Private Sub cmdFilter_Click()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
with sh.Range("A2")
.AutoFilter
.AutoFilter Field:=1, Criteria1:=tbName
end with
Next sh

Unload Me
End Sub