See if this works. Not sure if you have more than one workbook. I'm
opening a second workbook and putting the data in a new sheet in the
workbook where the macro is located..

Sub getdata()

fileToOpen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If fileToOpen = False Then
MsgBox ("Cannot open file - Exiting Macro")
Exit Sub
End If

Set bk = Workbooks.Open(Filename:=fileToOpen)

With ThisWorkbook
Set NewSht = .Sheets.Add(befo=.Sheets(1))
NewSht.Name = "Summary"

For Each Sht In bk.Sheets
If Sht.Name < "Summary" Then
With Sht
Set c = .Columns("A").Find(what:="OP", _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Could not find OP in sheet : " & Sht.Name)
Else
LastRow = .Range("E" & Rows.Count).End(xlUp).Row
FirstRow = LastRow - 4

If LastRow <= c.Row Then
MsgBox ("There are no rows to copy on sheet : " &
Sht.Name)
Else

If FirstRow <= c.Row Then
FirstRow = c.Row + 1
End If

Set Copyrange = .Range("B" & FirstRow & ":O" &
LastRow)

With NewSht
LastRow = .Range("E" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
Copyrange.Copy
.Range("B" & LastRow).PasteSpecial _
Paste:=xlPasteValues
End With
End If
End If
End With
End If
Next Sht
End With

bk.Close savechznges:=False
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=168586

Microsoft Office Help