Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
Ron, WorkbookB is open
Greg |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
Ron, thank you very much. I'll give it a try tomorrow and let you know
how it goes. Greg |
#6
|
|||
|
|||
Ron, as usual from you, works like a charm. Thanks
Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy - Paste Special | Excel Discussion (Misc queries) | |||
reminder notifications in a column | Excel Discussion (Misc queries) | |||
Excel won't copy outside sheet | Excel Discussion (Misc queries) | |||
Copy Function | Excel Discussion (Misc queries) | |||
Copy and pasting graphs to PowerPoint | Excel Discussion (Misc queries) |