Modify Excel VBA to Append Records Instead of Replacing a List
Question details
Modify an Excel VBA procedure to add copied records below an existing list rather than replace them.

- 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.
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.
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.
In Excel, open the workbook and press Alt+F11 to open the Visual Basic Editor. Open the module containing the copy procedure.
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)
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.
Use File > Save As and select Excel Macro-Enabled Workbook (*.xlsm) to retain the VBA code.

Share the Existing VBA Code as Text for a Tailored Fix
Use this approach when the source range, destination layout, or current procedure is unclear.
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.

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.




