VBA & Macro Problems

Use Excel VBA to Transfer Survey Scores and Preserve Totals

Anum MushtaqAnum Mushtaq Nov 20, 2026 868 views

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.

Use Excel VBA to Transfer Survey Scores and Preserve Totals
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.
Before you start

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.

Solution 1Recommended

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.

1
Set up the sheets

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.

2
Open the VBA editor

In Excel, select Developer > Visual Basic, then choose Insert > Module. If Developer is not visible, enable it in Excel's ribbon settings.

3
Add the macro

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

4
Save and test the workbook

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.

Use a VBA macro to record scores and update running totals
Keep inputs separate: The macro clears all contents in Entry!B2:B6, including formulas. Use those cells only for participant inputs, and enable macros only in workbooks you trust.
Free Microsoft Office alternative

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.

Organize survey inputs, score tables, and totals in spreadsheetsOpen and edit common Excel formats, including XLS and XLSXUse a familiar interface that makes migration easierWork with a free, lightweight office suite
microsoft office alternative - wps office

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.