VBA & Macro Problems

Prevent Excel AutoSum VBA from Repeating Totals on Every Sheet

Algirdas JasaitisAlgirdas Jasaitis Nov 18, 2026 869 views

Question details

The user needs an Excel AutoSum macro to write a total only on the intended worksheet.

Prevent Excel AutoSum VBA from Repeating Totals on Every Sheet
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, press Alt+F11, then open the module containing the AutoSum macro in the Project Explorer.

2
Set the target worksheet

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

3
Adjust the references

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.

4
Run and check the macro

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.

Qualify the target worksheet and ranges in the VBA macro
Check where the macro is stored: ThisWorkbook refers to the workbook containing the VBA code. If the macro is stored elsewhere, explicitly reference the workbook containing the target worksheet instead.
Free Microsoft Office alternative

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.

Add totals to individual worksheets with SUM formulas.Open and edit compatible Microsoft Excel XLSX and XLS files.Work in a familiar, easy-to-use spreadsheet interface.Move everyday spreadsheet work to a lightweight office suite.
microsoft office alternative - wps office

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.