VBA & Macro Problems

Modify Excel VBA to Append Records Instead of Replacing a List

Kushani NimanthikaKushani Nimanthika Nov 18, 2026 868 views

Question details

Modify an Excel VBA procedure to add copied records below an existing list rather than replace them.

Modify Excel VBA to Append Records Instead of Replacing a List
Product
Microsoft Excel
Device & OS
not provided
Scenario
A macro copies records from a source range to a destination worksheet that already contains data.
Observed behavior
The existing list is overwritten instead of the copied records being appended below it.
Before you start

Identify the source range, destination sheet and columns, and whether the source headers should be copied. Choose a destination column that is populated for every existing record; the example below uses column A.

Solution 1Recommended

Find the Next Destination Row Before Copying Records

Use the last populated row in the destination column to position new records below the existing list.

The following fragment assumes Srcws and Destws are already assigned to the correct worksheets. Adjust the source range and destination column to match your workbook.

The calculation appends after the last populated cell in column A; it does not fill gaps within the list. If column A can be blank for existing records, choose a consistently populated column instead.

1
Open the VBA procedure

In Excel, open the workbook and press Alt+F11 to open the Visual Basic Editor. Open the module containing the copy procedure.

2
Replace the fixed destination

Declare nextRow as a Long if it is not already declared, then replace the fixed destination in the copy operation with this example: Dim nextRow As Long nextRow = Destws.Cells(Destws.Rows.Count, "A").End(xlUp).Row + 1 If Application.WorksheetFunction.CountA(Destws.Columns("A")) = 0 Then nextRow = 1 Srcws.Range("A2:C10").Copy Destination:=Destws.Cells(nextRow, 1)

3
Adjust and test the range

Change A2:C10 to the actual source records and change Cells(nextRow, 1) if the destination starts in another column. Exclude the source header if the destination already has one, then run the macro from Developer > Macros and check that the records appear below the list.

4
Save the macro-enabled workbook

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

Find the Next Destination Row Before Copying Records
An empty destination starts at row 1: The example uses row 1 when column A is completely empty. If the destination needs a header, add it first or adjust the starting row.
Free Microsoft Office alternative

Work with Excel Spreadsheets in WPS Office

WPS Office is a free, lightweight alternative for working with spreadsheets in a familiar interface. Its Excel format compatibility helps you move existing files into WPS; the Excel VBA fix above still needs to be made in Excel.

Open and edit spreadsheet lists in a familiar interfaceCompatible with Microsoft Excel formats such as XLSXMove existing Office documents into WPS OfficeFree and lightweight for everyday spreadsheet work
microsoft office alternative - wps office

Frequently Asked Questions

Why does my macro overwrite existing Excel records?

The copy operation may use a fixed destination range that overlaps the list. Calculate the row after the last populated destination cell and use Destws.Cells(nextRow, 1) as the starting destination instead.

What if column A contains blanks in some records?

Use a destination column that is populated for every record in the Cells(Rows.Count, column).End(xlUp) calculation. Otherwise, the macro may identify a row above the end of the list.

How do I avoid copying the header each time?

Set the source range to begin below the header, such as A2:C10 instead of A1:C10, when the destination already has its header.

Why should I share VBA code as text instead of a screenshot?

Text can be copied into the VBA editor and reviewed directly. Include the source and destination ranges and specify whether headers should be copied.

What if a workbook copied from WeChat has an XLSM extension but will not open?

Check that the file was actually saved in Excel as a macro-enabled workbook using File > Save As, rather than merely renamed. If it still will not open, obtain another copy and check whether the transferred file is corrupted.