try this from the archives
posted by J.E
Sub SearchAllSheets()
Dim strSearchString As String
Dim ws As Worksheet
Dim foundCell As Range
Dim returnValue As Variant
Dim loopAddr As String
Dim countTot As Long
Dim counter As Long
strSearchString = InputBox(Prompt:= _
"Enter a title or other value to search for.", _
Title:="Search Workbook")
For Each ws In Worksheets
countTot = countTot + Application.CountIf( _
ws.UsedRange, "=" & strSearchString)
Next ws
If countTot = 0 Then
MsgBox strSearchString & " not found."
Else
counter = 0
For Each ws In Worksheets
With ws
.Activate
Set foundCell = .Cells.Find( _
What:=strSearchString, _
LookIn:=xlValues, _
LookAt:=xlPart)
If Not foundCell Is Nothing Then
loopAddr = foundCell.Address
Do
counter = counter + 1
foundCell.Activate
returnValue = MsgBox("Found " & strSearchString & _
" at " & foundCell.Address & vbNewLine & _
"(" & counter & " of " & countTot & ")", _
vbOKCancel)
If returnValue = vbCancel Then Exit For
Set foundCell = .Cells.FindNext( _
After:=foundCell)
Loop While Not foundCell Is Nothing And _
foundCell.Address <> loopAddr
End If
End With
Next ws
End If
End Sub 'SearchAllSheets
