Use Excel VBA to Transfer Survey Scores and Preserve Totals
Question details
The user needs to record each participant's survey scores, add them to running totals, and clear the input cells for the next respondent.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A survey workbook uses an input sheet for participant scores and a reference sheet for stored results and accumulated totals.
- Observed behavior
- Formulas linked to the input cells do not retain the original scores when those cells are cleared.
Decide which cells contain participant inputs and which cells will store recorded scores and totals. The example below uses two sheets named Entry and Reference and requires five numeric scores per participant.
Use a VBA macro to record scores and update running totals
Use this approach when each submission should be stored as values before the input cells are cleared.
This example stores each participant's five scores in a new row on Reference and keeps running totals in G2:G6. Adapt the sheet names and ranges if your workbook uses a different layout.
On Entry, reserve B2:B6 for the five scores. On Reference, reserve A:E for submission history, with headers in row 1, and G2:G6 for running totals. Leave the total cells blank or enter numeric starting values; do not put formulas in those cells.
In Excel, select Developer > Visual Basic, then choose Insert > Module. If Developer is not visible, enable it in Excel's ribbon settings.
Paste this code into the module: Sub RecordSurveyScores() Dim inputSheet As Worksheet, refSheet As Worksheet Dim i As Long, nextRow As Long Dim score As Variant, total As Variant Set inputSheet = ThisWorkbook.Worksheets("Entry") Set refSheet = ThisWorkbook.Worksheets("Reference") For i = 2 To 6 score = inputSheet.Cells(i, "B").Value total = refSheet.Cells(i, "G").Value If IsError(score) Or IsEmpty(score) Then MsgBox "Enter a numeric score in each input cell." Exit Sub End If If Not IsNumeric(score) Then MsgBox "Enter a numeric score in each input cell." Exit Sub End If If IsError(total) Then MsgBox "Running totals must be blank or numeric." Exit Sub End If If Not IsEmpty(total) Then If Not IsNumeric(total) Then MsgBox "Running totals must be blank or numeric." Exit Sub End If End If Next i nextRow = refSheet.Cells(refSheet.Rows.Count, "A").End(xlUp).Row + 1 If nextRow < 2 Then nextRow = 2 For i = 2 To 6 score = inputSheet.Cells(i, "B").Value total = refSheet.Cells(i, "G").Value If IsEmpty(total) Then total = 0 refSheet.Cells(nextRow, i - 1).Value = CDbl(score) refSheet.Cells(i, "G").Value = CDbl(total) + CDbl(score) Next i inputSheet.Range("B2:B6").ClearContents MsgBox "Scores recorded and inputs cleared." End Sub
Choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). Enter five test scores in Entry!B2:B6, then select Developer > Macros, choose RecordSurveyScores, and click Run. Check that Reference has a new history row and updated totals before entering another response.

Manage survey workbooks with WPS Spreadsheets
WPS Office is a free, lightweight, easy-to-use alternative with a familiar spreadsheet interface for opening and editing survey workbooks. It supports common Excel formats, making it easier to move existing files; check macro compatibility separately before relying on Excel VBA.

Frequently Asked Questions
Why don't formulas preserve the scores after I clear the input cells?
A formula normally recalculates from its source cells. If those cells are cleared, the formula no longer has the original scores. The macro writes scores as values to Reference before clearing Entry.
Where are individual responses and totals stored in the example?
Each submission is added to a new row in Reference columns A:E. The running totals for the five score positions are stored in Reference!G2:G6.
What happens if a score is missing or a total is not numeric?
The macro displays a message and exits before recording or clearing the inputs. Enter a numeric value in each cell in Entry!B2:B6 and make sure Reference!G2:G6 contains only blank or numeric values.
Can I run the macro from a button?
In Excel, use Developer > Insert to add a form control button, then assign RecordSurveyScores to it. Clicking the button runs the same transfer and clearing process.
Can WPS Spreadsheets run this Excel VBA macro?
VBA support can vary by WPS edition and configuration, so test the macro in your environment. WPS can open common Excel spreadsheet formats, but opening a workbook does not guarantee that its VBA automation will run.




