Hi, I'm a newbie to VBA and am struggling with this bit of coding so I
would really appreciate any help! I need to be able to search just
certain worksheets in the workbook as it's important that the find does
not extend to the other worksheets. The user enters the reference
number in a textbox on "Sheet 1" and it should look through just
certain sheets only and where it finds the matching value, to make that
the active cell. I don't know what I need to add to make my coding work
here. At the moment it won't find any matches and keeps making "sheet
2" the active sheet. (for speed it only needs to search column B, from
B5 onwards, on these sheets)

Private Sub CommandButton2_Click()
On Error Resume Next
Dim Findstring As String
Dim Rng As Range
Dim mysheet As Worksheets
Findstring = TextBox1.Text
Worksheets(Array("Sheet 2", "Sheet 4", _
"Sheet 6", "Sheet 8")).Select

For Each mysheet In Worksheets

If Findstring < "" Then
Set Rng = Columns(B).Find(What:=Findstring, _
After:=Range("B5"), _
LookIn:=xlValues, _
LookAt:=x1Whole, _
MatchCase:=False).Activate
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "No box found"
TextBox1 = ""
End If
Else
MsgBox "Please enter the box ref"
End If
Next mysheet

End Sub


--
Blondegirl
------------------------------------------------------------------------
Blondegirl's Profile: http://www.excelforum.com/member.php...o&userid=29615
View this thread: http://www.excelforum.com/showthread...hreadid=532075