MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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?



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
Location = F.Address
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...