Copy to Location
 

I have a worksheet in workbookA that I need to copy the rows from Row 9
to the last used row in the workbook to workbookB. The insertion point
in WorkbookB for the copied rows would be to the row where Column "E"
has the words "Accruals". Any help would be appreciated. TIA


Greg

Hi Greg

Is workbook B open or must the macro do that


--
Regards Ron de Bruin
http://www.rondebruin.nl


"GregR" wrote in message oups.com...
I have a worksheet in workbookA that I need to copy the rows from Row 9
to the last used row in the workbook to workbookB. The insertion point
in WorkbookB for the copied rows would be to the row where Column "E"
has the words "Accruals". Any help would be appreciated. TIA


Greg

Ron, WorkbookB is open

Greg

Try this example Greg

Change the workbooks names and the sheet names if needed
I use the sheet name "Sheet1"


Sub test()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim last As Long
Dim copyrow As Long
Dim Rng As Range

Set wb1 = Workbooks("Booktest1.xls")
Set wb2 = Workbooks("Booktest2.xls")
last = LastRow(wb1.Sheets("Sheet1"))


Set Rng = wb2.Sheets("Sheet1").Range("E:E").Find(What:="Accr uals", _
After:=Range("E" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
copyrow = Rng.Row + 1
wb1.Sheets("Sheet1").Range(wb1.Sheets("Sheet1").Ro ws(9), wb1.Sheets("Sheet1").Rows(last)).Copy _
wb2.Sheets("Sheet1").Cells(copyrow, "A")

Else
MsgBox "Nothing found"
End If
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


--
Regards Ron de Bruin
http://www.rondebruin.nl


"GregR" wrote in message oups.com...
Ron, WorkbookB is open

Greg

Ron, thank you very much. I'll give it a try tomorrow and let you know
how it goes.

Greg

Ron, as usual from you, works like a charm. Thanks

Greg