Copy to Location

[GregR]

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 de Bruin]

Hi Greg

Is workbook B open or must the macro do that

 

[GregR]

Ron, WorkbookB is open

Greg

 

[Ron de Bruin]

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:="Accruals", _
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").Rows(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

 

[GregR]

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

Greg

 

[GregR]

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

Greg