VBA & Macro Problems

Update an Excel VBA Macro to Append Monthly Data Without Overwriting

Phi Hung VoPhi Hung Vo Nov 18, 2026 868 views

Question details

Update a VBA macro so each monthly data set is added after existing records.

Update an Excel VBA Macro to Append Monthly Data Without Overwriting
Product
Excel VBA
Device & OS
not provided
Scenario
A monthly update needs to be copied from a source worksheet to a destination worksheet.
Observed behavior
The existing macro overwrites records instead of pasting the new data into the next available row.
Before you start

Identify the source and destination sheet names, the columns to copy, and a key column that is populated for every record. The example uses column A as the key, columns A:D as the data, and row 1 as the header.

Solution 1Recommended

Find the Next Destination Row Before Copying Data

Use the last populated cell in a reliable key column to append each monthly update below existing records.

The example copies values from columns A:D, starting at row 2 of the source sheet. It does not copy formatting or formulas, and it assumes column A is populated for every source and destination record.

1
Open the VBA editor

In Excel, open the workbook and select Developer > Visual Basic. In the editor, open the module containing your existing macro, or select Insert > Module to create one.

2
Update the macro

Replace or adapt the copy-and-paste section with the following example. Change "Source" and "Destination" to the actual worksheet names and adjust the column range if needed: Sub AppendMonthlyData() Dim wsSource As Worksheet, wsDest As Worksheet Dim lastSourceRow As Long, nextRow As Long, rowCount As Long On Error Resume Next Set wsSource = ThisWorkbook.Worksheets("Source") Set wsDest = ThisWorkbook.Worksheets("Destination") On Error GoTo 0 If wsSource Is Nothing Or wsDest Is Nothing Then MsgBox "Check the source and destination sheet names." Exit Sub End If If wsSource Is wsDest Then MsgBox "Choose different source and destination sheets." Exit Sub End If lastSourceRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row If lastSourceRow < 2 Then MsgBox "No source records found below the header." Exit Sub End If If Application.WorksheetFunction.CountA(wsDest.Columns("A")) = 0 Then nextRow = 1 Else nextRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row + 1 End If rowCount = lastSourceRow - 1 wsDest.Cells(nextRow, "A").Resize(rowCount, 4).Value = _ wsSource.Cells(2, "A").Resize(rowCount, 4).Value End Sub

3
Test and save the macro

In the VBA editor, place the cursor in the macro and select Run > Run Sub/UserForm. Check that the new rows appear below the destination records, then use File > Save As to save the workbook as an Excel Macro-Enabled Workbook (.xlsm).

Find the Next Destination Row Before Copying Data
Check the key column: If destination column A is completely empty, the example starts at row 1. Add a header first if your destination sheet needs one. Records with blank key cells can cause the next row to be identified incorrectly.
Append monthly records in WPS Spreadsheets

Use a VBA Macro to Append Data in WPS Spreadsheets

In a desktop edition of WPS Spreadsheets with VBA support enabled, adapt the macro above to find the next populated row and add monthly values below it. VBA availability and setup depend on your WPS edition and platform.

  1. 1. Check VBA support: Open WPS Spreadsheets and check whether Developer > Visual Basic is available. Your edition may require a VBA component or a license that supports macros.
  2. 2. Open the workbook: Use File > Open to load the workbook, then confirm the source and destination sheet names and the key column.
  3. 3. Edit the macro: Select Developer > Visual Basic, open a module, and adapt the append macro above to your worksheet names and data columns.
  4. 4. Test and save: Run the macro from the VBA editor, verify the appended rows, and use File > Save As to retain the workbook in .xlsm format.
Append monthly records using a key columnOpen and save compatible Excel macro-enabled .xlsm workbooksWork in a familiar spreadsheet interfaceUse WPS Office's free spreadsheet tools
QA img-9

Frequently Asked Questions

Why does my macro overwrite existing rows?

It may paste into a fixed destination such as A2. Calculate the next row with wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row + 1, then use that row as the destination.

Which column should I use to find the next row?

Use a key column that contains a value for every destination record. Replace "A" in the next-row calculation with that column's letter if needed.

Does the example copy formulas and formatting?

No. The .Value assignment copies the displayed cell values, not formulas or formatting. Use a different copy method if those need to be preserved.

Will running the macro twice duplicate the monthly update?

Yes. The example appends the source rows each time it runs; it does not check for duplicates. Add a unique-record check or clear the source update after verifying the append if duplicates are a concern.

Why can't I find the VBA editor in WPS Spreadsheets?

VBA support varies by WPS edition and platform. Check whether Developer > Visual Basic is available and whether your installation requires an additional VBA component or a supported license.