Hi All,

I have several sheets with several columns of data.

I want to search for an item in column A starting from
Fourth sheet until sheet 15 and if the item found then
display in a
message box (which I prefer) or in a new sheet the data
label(for column A,C,F,K,L) and corresponding data (for
selected fields) or proceed to the next sheet until the
item is found. the data never get repeated (In column A)
so there would be only one instance of the data in column
A.

Once the item is found no need to continue search.

If it is not found a message saying not found.

Is it possible to search through sheets which
xlVeryHidden without setting it to be visible?

How could i get this by a macro?

My present code look like this:

Sub TestSearch()

Sheets("Interface").Select
sStr = InputBox("Enter item to search for")
For Each sh In ThisWorkbook.Worksheets
If sStr < "" Then
Set rng = Nothing
sh.Activate
Set rng = sh.Range("B:B").Find(What:=sStr, _
After:=sh.Range("B1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End If
If Not rng Is Nothing Then
MsgBox "found on sheet " & sh.Name & " at cell " &
rng.Address

'Else
Exit Sub
End If


Next
If rng Is Nothing Then
MsgBox sStr & " was Not found"
End If
End Sub


TIA
Soniya