Copy last row in File 1 to next available blank row in File 2

Posted by Olivier on February 09, 2002 6:51 AM

Hello

I would like in File 1 to copy the last row, colums C through G into File 2 at the next available blank row

I think a VBA procedure is needed and I am just a beginner

Thanks a lot

Posted by Derek on February 09, 2002 8:24 AM

Olivier
Try this code:

Dim NextRow As Long
Application.ScreenUpdating = False
NextRow = Range("C65536").End(xlUp).Row '+ 1
Cells(NextRow, 3).Select
Range(ActiveCell, ActiveCell.Offset(0, 4)).Copy
Windows("File2.xls").Activate
NextRow = Range("C65536").End(xlUp).Row + 1
Cells(NextRow, 3).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("File1.xls").Activate
Application.ScreenUpdating = True
End Sub

Hope this helps
Derek

Posted by Olivier on February 09, 2002 10:14 AM

Derek

Thanks a lot. It works wonderfully

Posted by Guthrum on February 09, 2002 11:38 AM

Or ....

Dim source As Range, dest As Range
Set source = Workbooks("File1.xls").Worksheets("Sheet1").Range("C65536").End(xlUp).Resize(, 5)
Set dest = Workbooks("File2.xls").Worksheets("Sheet1").Range("C65536").End(xlUp).Resize(, 5).Offset(1, 0)
source.Copy dest

Posted by Olivier on February 09, 2002 12:23 PM

Thanks again Guthrum

. It works wonderfully