Prevent Excel AutoSum VBA from Repeating Totals on Every Sheet
Question details
The user needs an Excel AutoSum macro to write a total only on the intended worksheet.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A VBA macro adds a SUM formula to a worksheet in a workbook containing multiple sheets.
- Observed behavior
- The macro repeats totals across worksheets instead of limiting the operation to the intended sheet.
Identify the target worksheet, the cells to sum, and the cell where the total should appear. Confirm that the macro is stored in the target workbook if you plan to use ThisWorkbook.
Qualify the target worksheet and ranges in the VBA macro
Use an explicit worksheet reference to write the SUM formula on only the intended sheet.
Unqualified references such as Range, Cells, ActiveSheet, and Selection can point to the active sheet rather than the intended one. Remove or revise any loop that applies the total to multiple worksheets.
In Excel, press Alt+F11, then open the module containing the AutoSum macro in the Project Explorer.
Replace the relevant total-writing code with an explicitly qualified reference, for example: Sub AddTotal() Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("Sheet1") ws.Range("A11").Formula = "=SUM(A2:A10)" End Sub
Change Sheet1 to the exact target sheet name, A2:A10 to the values to sum, and A11 to the desired total cell. Qualify any other Range or Cells references in the same operation with ws.
Return to Excel, open Developer > Macros, select the macro, and click Run. Check that the formula appears in the target cell and that no new totals were added to other sheets.

Create worksheet totals with WPS Spreadsheet
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and use SUM formulas to create totals without relying on the Excel macro.

Frequently Asked Questions
Why does my macro add totals to several worksheets?
A loop over worksheets or unqualified Range and Cells references can cause the total to be written on unintended sheets. In the VBA editor, inspect the total-writing code and explicitly reference the target worksheet.
What does ThisWorkbook mean in VBA?
ThisWorkbook is the workbook containing the macro code. If your macro is in a different workbook, use an explicit reference to the workbook that contains the target sheet.
How do I change the cells included in the total?
In the formula assignment, replace =SUM(A2:A10) with the range you need, and change ws.Range("A11") to the cell where the result should appear.
How can I keep the macro when saving the workbook?
In Excel, select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). A standard XLSX file cannot retain VBA macros.




