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. :(


--
Lizz45ie
------------------------------------------------------------------------
Lizz45ie's Profile: http://www.excelforum.com/member.php...o&userid=23410
View this thread: http://www.excelforum.com/showthread...hreadid=483313

try sheet1.activate

--
RPIJ
-----------------------------------------------------------------------
RPIJG's Profile: http://www.excelforum.com/member.php...nfo&userid=928
View this thread: http://www.excelforum.com/showthread.php?threadid=48331

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


--
Lizz45ie


------------------------------------------------------------------------
Lizz45ie's Profile: http://www.excelforum.com/member.php...o&userid=23410
View this thread: http://www.excelforum.com/showthread...hreadid=483313

replace sheet1.select with sheet1.activat

--
RPIJ
-----------------------------------------------------------------------
RPIJG's Profile: http://www.excelforum.com/member.php...nfo&userid=928
View this thread: http://www.excelforum.com/showthread.php?threadid=48331

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


--
HTH...

Jim Thomlinson


"Lizz45ie" wrote:


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. :(


--
Lizz45ie
------------------------------------------------------------------------
Lizz45ie's Profile: http://www.excelforum.com/member.php...o&userid=23410
View this thread: http://www.excelforum.com/showthread...hreadid=483313