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

 

[Jo4x4]

My big problem is getting the newest data to go to the next open row on the destination sheet.

Thanks
Jo

Win XP office 2007

 

[John Davis]

My big problem is getting the newest data to go to the next open row on the destination sheet.

Thanks
Jo

Win XP office 2007

Does this help?

Sub JO4X4()
Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

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

End Sub

 

[Jo4x4]

Thanks John, it works fine, but is there a way to "wake up" a closed destination file, or does it need to be open?

Thanks again.
Jo

 

[John Davis]

Thanks John, it works fine, but is there a way to "wake up" a closed destination file, or does it need to be open?

Thanks again.
Jo

I think it has too be open, but you can open, copy to, and then close it:

Sub JO4X4()
Dim lr As Long

lr = 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


End Sub

 

[Jo4x4]

Thanks again John.

Jo

 

[John Davis]

Thanks again John.

Jo

You're welcome. Glad I could help.

 

[Jo4x4]

Hi John, it is giving me an error "subscript out of range"

I know preciously little about coding, but shouldn't the workbook be open before we try to find the next row?

Jo

 

[John Davis]

Hi John, it is giving me an error "subscript out of range"

I know preciously little about coding, but shouldn't the workbook be open before we try to find the next row?

Jo

Change this:

lr = Cells(Rows.Count, 1).End(xlUp).Row

To this:

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

 

[Jo4x4]

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