VBA & Macro Problems

Append Excel Cell Values to a Growing Table with a VBA Macro

Natalie TaylorNatalie Taylor Nov 12, 2026 869 views

Question details

Save a name and a calculated value from source cells as a new record each time, without replacing records that were saved earlier.

Append Excel Cell Values to a Growing Table with a VBA Macro
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
The user repeatedly enters or calculates values in a worksheet and wants a button-driven process that adds each pair to a growing records table.
Observed behavior
The desired result is for each save action to write values to the next empty row and retain all existing rows.
Before you start

Create a destination worksheet with headers in row 1, such as Name in column A and Value in column B. Update the worksheet names and source cell references in the macro to match your workbook before running it.

Solution 1Recommended

Use a VBA macro to append source cell values to the next empty row

Use this method when the destination is a normal worksheet range and you need each button click to preserve prior records.

The macro finds the next available row in the records sheet, then writes the source cells as fixed values. This stores the current calculated result rather than a formula that could change later.

1
Prepare the input and records sheets

Create an input sheet named Input and a records sheet named Records. In Records, enter Name in A1 and Value in B1. In this example, the name is read from Input!B2 and the calculated value is read from Input!C2.

2
Open the VBA editor and add a module

In Excel, press Alt+F11, select Insert > Module, and paste the macro into the new code module.

3
Add the append-record macro

Paste this code: Sub SaveRecord() Dim wsInput As Worksheet Dim wsLog As Worksheet Dim nextRow As Long Set wsInput = ThisWorkbook.Worksheets("Input") Set wsLog = ThisWorkbook.Worksheets("Records") If Trim(CStr(wsInput.Range("B2").Value)) = "" Then MsgBox "Enter a name before saving.", vbExclamation Exit Sub End If nextRow = wsLog.Cells(wsLog.Rows.Count, "A").End(xlUp).Row + 1 If nextRow < 2 Then nextRow = 2 wsLog.Cells(nextRow, "A").Value = wsInput.Range("B2").Value wsLog.Cells(nextRow, "B").Value = wsInput.Range("C2").Value MsgBox "Record saved to row " & nextRow & ".", vbInformation End Sub

4
Assign the macro to a button

Return to the worksheet, choose Developer > Insert > Button (Form Control), draw the button on the Input sheet, and select SaveRecord when Excel asks which macro to assign.

5
Save and test the workbook

Save the file as an Excel Macro-Enabled Workbook (*.xlsm). Enter a name, confirm the calculated value in Input!C2, and click the button; the pair is added to the next row in Records.

Use a VBA macro to append source cell values to the next empty row
Previous records remain unchanged: Because the macro calculates nextRow from the last used cell in column A, it appends each new record instead of writing over an earlier one.
Work with spreadsheet macros in WPS Office

Append Repeated Spreadsheet Records with WPS Spreadsheets

WPS Spreadsheets provides a familiar worksheet workflow for recording names and calculated values in a growing log. Open the workbook, use the macro tools available in your WPS Office edition, and assign an append-record macro to a button for repeatable data entry.

  1. 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the XLSX or XLSM workbook containing the Input and Records sheets.
  2. 2. Set up the records headers: On the Records sheet, enter headers such as Name and Value in the first row, leaving the rows below available for appended entries.
  3. 3. Access macro tools: Open the Developer or macro tools area in WPS Spreadsheets and create or open the workbook macro module, depending on the features available in your edition.
  4. 4. Use an append-record routine: Add a routine that identifies the next empty row in the records column and writes the current input name and calculated value as values.
  5. 5. Run the routine for each entry: Use the macro command or an assigned worksheet button to add a new record whenever the input cells are ready.
Add each name and calculated result to the next available spreadsheet row.Use a familiar Excel-style worksheet layout for input forms and record logs.Open and edit Microsoft Excel XLSX files with compatible spreadsheet formatting.Work with Excel macro-enabled XLSM workbooks when VBA macro support is available in your WPS Office edition.
microsoft office alternative - wps office

Frequently Asked Questions

How does the macro avoid overwriting existing Excel records?

It uses Cells(Rows.Count, "A").End(xlUp).Row + 1 to locate the row after the last used cell in column A. The new name and value are written to that next row.

Will the saved calculated value change if the source formula changes later?

No. The macro assigns the cell's Value property to the records sheet, so it stores the calculated result at the time you click the button rather than copying the formula.

What should I change if my source cells are not B2 and C2?

Replace wsInput.Range("B2") and wsInput.Range("C2") in the VBA code with your actual source cell addresses. Also update the Input and Records worksheet names if yours are different.

Why should I save the workbook as an XLSM file?

An XLSM file preserves VBA code. If you save the workbook as XLSX, Excel removes the macro and the assigned button will no longer run it.

Can I add more fields, such as a date or an order number?

Yes. Add headers in additional columns on the Records sheet, then add matching lines such as wsLog.Cells(nextRow, "C").Value = wsInput.Range("D2").Value to the macro.