No, the macro can be placed in the personal.xls file and executed on the
active workbook. If you don'thave a personal.xls already, go to
Tools=Macro=Record a Macro and then select to save the macro in the
personal workbook. Then select a cell or two and turn off macro recording.
This will create the personal.xls file. This is a standard workbook stored
in the xlStart directory and opens as a hidden window whenever excel is
opened manually. Macros placed here will be visible when you do
Tools=Macro=Macros.

PERSONAL.XLS!SearchSheets

More sheets would not be a problem as the macro makes no assumption about
number of sheets


If multiple columns to look at you could do this


Sub SearchSheets()
Dim ans As String, rng As Range
Dim sh As Worksheet, saddr As String
Dim res As Long
Const col as String = "H:H,J:J"
ans = InputBox("Enter flight number: ")
If Len(Trim(ans)) = 0 Then Exit Sub
For Each sh In Worksheets
set rng1 = sh.Range(col)
Set rng = _
rng1.Find(What:=ans, _
After:=rng1(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
saddr = rng.Address
Do
Application.Goto rng, True
res = MsgBox("Continue Searching?", vbYesNo)
If res = vbNo Then Exit Do
Set rng = rng1.FindNext(rng)
Loop While rng.Address < saddr
End If
If res = vbNo Then Exit For
Next
End Sub

change
Const col as String = "H:H,J:J"

to reflect the columns that need to be examined.

--
Regards,
Tom Ogilvy


"acss" wrote:

Thanks Tom,

I tried the find after grouping and it works but the manual process of
grouping can be a drag. In the option of creating the macro, I gues it would
have to be inserted into the new workbook sent to me monthly. In using the
macro, what change is needed if there is a different column or additional
sheets?

Thanks for the help

"Tom Ogilvy" wrote:

You could group all the sheet and to Edit=Find

if you want a macro:

Sub SearchSheets()
Dim ans As String, rng As Range
Dim sh As Worksheet, saddr As String
Dim res As Long
ans = InputBox("Enter flight number: ")
If Len(Trim(ans)) = 0 Then Exit Sub
For Each sh In Worksheets
Set rng = _
sh.Columns(8).Find(What:=ans, _
After:=sh.Range("H65536"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
saddr = rng.Address
Do
Application.Goto rng, True
res = MsgBox("Continue Searching?", vbYesNo)
If res = vbNo Then Exit Do
Set rng = sh.Columns(8).FindNext(rng)
Loop While rng.Address < saddr
End If
If res = vbNo Then Exit For
Next
End Sub

--
regards,
Tom Ogilvy


"acss" wrote:

I have a workbook that that is sent to me on a monthly basis and it contains
8 sheets of data. I spent a huge amount of time searching flight numbers on
column H. Is there a way to automate the search so i do not have to go to
each sheet and search column H row by row?