Copy and Append Rows to Another Excel Sheet with VBA
Question details
Copy one or more source rows to another worksheet and append them below existing data using VBA.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A workbook has source records on one sheet and needs to add them to a destination sheet without replacing existing records.
- Observed behavior
- Each copied row should be placed after the last existing destination record.
Check the source and destination sheet names and confirm that column A identifies the last record on both sheets. The example copies columns A through S, starting at source row 39.
Use a VBA macro to append source rows to the destination sheet
Use this macro to copy row 39 and any subsequent source rows below the existing records on Sheet2.
In Excel, open the workbook and select Developer > Visual Basic. If Developer is not visible, enable it under File > Options > Customize Ribbon.
In the VBA editor, select Insert > Module and paste: Sub AppendRows() Dim src As Worksheet, dst As Worksheet Dim lastSource As Long, nextRow As Long, i As Long Set src = ThisWorkbook.Worksheets("Sheet1") Set dst = ThisWorkbook.Worksheets("Sheet2") lastSource = src.Cells(src.Rows.Count, 1).End(xlUp).Row If lastSource < 39 Then Exit Sub If Application.WorksheetFunction.CountA(dst.Columns(1)) = 0 Then nextRow = 1 Else nextRow = dst.Cells(dst.Rows.Count, 1).End(xlUp).Row + 1 End If For i = 39 To lastSource src.Range(src.Cells(i, 1), src.Cells(i, 19)).Copy Destination:=dst.Cells(nextRow, 1) nextRow = nextRow + 1 Next i End Sub
Change Sheet1, Sheet2, row 39, or column 19 in the module if needed. To copy only row 39, replace the lastSource assignment with lastSource = 39. Then select Developer > Macros, choose AppendRows, and click Run.
Select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm) to retain the VBA code.

Work with spreadsheet records in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and continue working with records in WPS Spreadsheets; VBA macro support can vary, so test macros before relying on them.

Frequently Asked Questions
How does the macro find where to append the rows?
It uses the last nonempty cell in column A of Sheet2 and starts copying on the next row. If column A is empty, it starts at row 1.
How can I copy only one source row?
In the VBA module, replace the lastSource assignment with lastSource = 39. Change both that value and the loop's starting row if you want to copy a different row.
Will the macro skip blank rows?
No. It copies every row from row 39 through the last source row identified in column A, including any blank rows between records.
Why does Excel remove my macro when I save?
A standard XLSX file does not retain VBA code. Select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm).
Will the macro copy the same records again if I rerun it?
Yes. Each run appends the selected source rows again. Change the source range or add duplicate-checking logic if records should be copied only once.




