On Tuesday, May 6, 2014 10:38:15 AM UTC-7, GS wrote:
Final version:



Sub FindSheetsWithID()

' Looks for an ID on all sheets except "Sheet1",

' and notifies the result of the search.

Dim ws As Worksheet, rng As Range

Dim sID$, sIdShts$, sMsg$

Dim bFoundID As Boolean



sID = InputBox("Enter a Client ID numbet")

If Trim(sID) = "" Then Exit Sub



For Each ws In ThisWorkbook.Worksheets

If Not ws.Name = "Sheet1" Then

Set rng = ws.UsedRange.Find(What:=sID, _

LookIn:=xlValues, _

LookAt:=xlWhole, _

SearchOrder:=xlByColumns)

If Not rng Is Nothing Then

bFoundID = True

sidhts = sidhts & ",'" & ws.Name & "'!" & rng.Address

End If

End If

Next ws

If bFoundID Then

sMsg = "The ID (" & sID & ") was found on the following sheets:"

sMsg = sMsg & vbLf & vbLf

sMsg = sMsg & Join(Split(Mid(sIdShts, 2), ","), vbLf)

Else

sMsg = "ID not found"

End If

MsgBox sMsg

End Sub



--

Garry


Works for me!!! Nice indeed.

Small typo on this line but quite fixable even by me.

sidhts = sidhts & ",'" & ws.Name & "'!" & rng.Address

I like. Thanks.

Howard