VBA & Macro Problems

Copy and Append Rows to Another Excel Sheet with VBA

Phi Hung VoPhi Hung Vo Nov 18, 2026 869 views

Question details

Copy one or more source rows to another worksheet and append them below existing data using VBA.

Copy and Append Rows to Another Excel Sheet with 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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, open the workbook and select Developer > Visual Basic. If Developer is not visible, enable it under File > Options > Customize Ribbon.

2
Insert the macro

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

3
Adjust the range and run

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.

4
Save the macro

Select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm) to retain the VBA code.

Use a VBA macro to append source rows to the destination sheet
Check column A: The macro finds the last row using column A and copies every row in the selected range, including gaps. Make sure column A is populated for each record and that the calculated destination rows do not contain data in other columns.
Free Microsoft Office alternative

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.

Copy and organize rows across worksheets using familiar spreadsheet controlsCompatible with common Microsoft Excel formats, including XLSX and XLSFamiliar interface helps make moving from Microsoft Office easierFree, lightweight suite for everyday document and spreadsheet work
QA img-9

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.