Appending a row from one worksheet to a worksheet in a different workbook

Question

I have a workbook that produces a single row of data for each month. I want to append that data into a running list of previous months in a different workbook.

The code copies the data from the worksheet then pastes the data into the other worksheet.

My problem is it will just paste in the first row. I want to paste at the bottom of any data that's already there.

Private Sub CommandButton1_Click()

Dim wbSource As Workbook
Dim wbTarget As Workbook
Dim shSource As Worksheet
Dim shTarget As Worksheet
Dim LastRow As Long

Set wbSource = ThisWorkbook
Set wbTarget = Workbooks.Open(Filename:="Y:\DEVTEST.xlsx")

Set shSource = wbSource.Worksheets("Append")
shSource.Range("A2:U2").Copy

' Reference to sheet to copy to
Set shTarget = wbTarget.Worksheets("Sheet1")

LastRow = WorksheetFunction.Max(Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row)
shTarget.Cells(LastRow, "A").PasteSpecial xlPasteValues

End Sub

[Here's how to find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). — BigBen, Feb 02 '21 at 02:36
Great article, I made some of the changes to my original post based on what I saw in there, but it seems to just overwrite an existing row. Any thoughts on where I'm going wrong here? — Codegameproper, Feb 02 '21 at 03:11

score 0 · Answer 1 · answered Feb 02 '21 at 05:24

Copy Range to Another Workbook

When you need to copy values, it is more efficient to just write the values to the other worksheet than using Copy/PasteSpecial.

Option Explicit

Private Sub CommandButton1_Click()

    Dim wbSource As Workbook
    Dim wbTarget As Workbook
    Dim shSource As Worksheet
    Dim shTarget As Worksheet
    Dim rg As Range
    Dim cel As Range
    
    Set wbSource = ThisWorkbook
    Set shSource = wbSource.Worksheets("Append")
    Set rg = shSource.Range("A2:U2")
    
    Set wbTarget = Workbooks.Open(Filename:="Y:\DEVTEST.xlsx")
    Set shTarget = wbTarget.Worksheets("Sheet1")
    Set cel = shTarget.Cells(shTarget.Rows.Count, "A").End(xlUp).Offset(1)
    
    cel.Resize(, rg.Columns.Count).Value = rg.Value

End Sub

Appending a row from one worksheet to a worksheet in a different workbook

1 Answers1

Copy Range to Another Workbook