Copy data to the next row on a separate workbook

[Jo4x4]

Hi everybody,

I have six columns of data that I would like to copy to another workbook. The workbooks have the same column headings. The source sheet is monthly, and the destination sheet is a yearly total.
So I want anything that changes on the source sheet to be updated to the next available row in the destination sheet, either automatically or via a VBA button.

Any help will be appreciated

Thanks
Jo

Win XP, Office 2007

 

[John Davis]

Hi John,

It is working now. Stupid error on my side.

It is however giving me an error/warning that my destination sheet already contains a field called "date". When I then tell it to continue, it does everything perfectly.

Any ideas on what I can change to avoid the warning?

Thanks
Jo

You could set Display Alerts too False, then the code, reset it to True:

Sub JO4X4()
Dim lr As Long

With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

lr = Workbooks("Source.xls").Sheets("Source").Cells(Rows.Count, 1).End(xlUp).Row 

Workbooks.Open Filename:="D:\Common\data\Destination.xls"

Workbooks("Source.xls").Sheets("Source").Range(Range("A2"), Range("F" & lr)).Copy Workbooks("Destination.xls").Sheets("Destination").Range("A" & Rows.Count).End(xlUp)(2)

Workbooks("Destination.xls").Close SaveChanges:=True

Workbooks("Source.xls").Activate

With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
End With

End Sub

 

[Jo4x4]

Thanks. Whenever you are in South Africa, I owe you a beer!

Jo

 

[John Davis]

Thanks. Whenever you are in South Africa, I owe you a beer!

Jo

You're welcome. I think I've had some South African brewed before. If I remember correctly, it's a bit more stronger than my local favorite Budweiser. And if I'm ever in that part of the world, I'll surely take you up on the offer. Glad too help.