Sub copyData()
Dim sh As Worksheet, sh1 As Worksheet
Dim rng As Range, rng1 As Range
Dim fName As String
Dim sAddr As String
Dim bk As Workbook

' sheet to copy data to:
Set sh1 = ThisWorkbook.Worksheets("Sheet1")

fName = Dir("C:\MyFolder\*.xls")
Do While fName < ""
Set bk = Workbooks.Open("C:\Myfolder\" & fName)
For Each sh In bk.Worksheets
Set rng = sh.Cells.Find("03-*", LookIn:=xlValues, _
Lookat:=xlWhole, MatchCase:=False)
If Not rng Is Nothing Then
sAddr = rng.Address
Do
Set rng1 = sh1.Cells(Rows.Count, 1).End(xlUp)(2)
rng.EntireRow.Copy Destination:=rng1
rng1.Insert Shift:=xlShifttoRight
' uncomment to get sheet name too
rng1.Offset(0,-1).Value = bk.Name ' & "!" & rng.Parent.Name
Set rng = sh.Cells.FindNext(rng)
Loop While rng.Address < sAddr
End If
Next sh
' added line to close workbook
bk.Close Savechanges:=False
fName = Dir()
Loop
End Sub

--
Regards,
Tom Ogilvy



"Ann" wrote in message
...
Works like a charm!

One other thing, more convenience than anything, is there a way to have it
add the originating file name to the row of data it returns?

Such as:
"FileOne.xls" "03-54345"

Thanks so much!

"Tom Ogilvy" wrote:

Loop while rng.Address < sAddr

--
Regards,
Tom Ogilvy

"Ann" wrote in message
...
I am getting a "Compile Error: syntax error" for this line:
Loop rng.Address < sAddr

Otherwise, should work ok, all workbooks are in the same directory and
I
want to search all of them.

Any help? What am I missing?

Thanks,
Ann