Sub AllMySheets()

' 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



sIdShts = ",'" & 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, s), ","), vbLf)

Else

sMsg = "ID not found"

End If

MsgBox sMsg

End Sub



--

Garry


Should this line be

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

Be like this? (sIdShts, sID)

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


Does not include the sheet name in my test.

Howard