Macro to seach all sheets
Posted by Mike on August 08, 2001 10:56 PM
Hi all,
I need a macro that searches all the sheets in a workbook for a name (which is put into a nominated cell by the user) and activates the sheet containing the required name.
Any ideas?
Ta
-Mike
Posted by Rob Jackson on August 09, 2001 2:29 AM
Try this:
Sub finder()
SearchString = "Rob"
Set S = Sheets.Application
For Each S In Application.Sheets
With S.Range("A1:IV65536")
Set F = .Find(SearchString, LookIn:=xlValues)
If F Is Nothing Then
Else
Location = F.Address
S.Select
Range(Location).Select
Exit For
End If
End With
Next S
End Sub
You will need to tweak it slighty to pull the location from a cell or form. Also there are other options on the find function you can set.
Happy hunting...
