On Jan 10, 8:33*pm, joel wrote:
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

Hi Joel,

Thanks for your prompt reply
After I run your codes and the result copies the wrong range
Your codes copy the adjacent range at the last used rows ( ie wrong
range ), instead it should copy the row starting below immediately
after the row which found "OP" in cloumn A until the last used rows
from column B to column O
The correct range to copy should cover the current region starting row
"OP" until the last used row from column B to column O

I try to fix your codes but it does not work

Regards
Len