Append Excel Cell Values to a Growing Table with a VBA Macro
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.

- 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.
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.
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.
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.
In Excel, press Alt+F11, select Insert > Module, and paste the macro into the new code module.
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
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.
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.

Append records to an Excel Table with a ListRows VBA macro
Use this option when your destination is an actual Excel Table and you want each new record to automatically become part of that table.
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. 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. 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. 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. 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. 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.

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.




