VBA & Macro Problems

How to Calculate Section Totals and Gross Profit with VBA in Excel

Maira Mehtab Sep 20, 2026 869 views

Question details

The user requires a dynamic Excel VBA solution to automatically calculate section subtotals, total columns, year totals, grand totals, and gross profit across multiple worksheets (excluding specific ones), while ensuring cell formatting is not overridden during the formula filling process.

Product: Excel
Device & OS: not provided
Scenario: Automating complex financial calculations across multiple worksheets using a dynamic VBA macro.
Observed behavior: Generating dynamic totals across sheets using the FillRight method causes existing cell formatting to be overwritten and ruined.

Before you start

Ensure you have a backup of your workbook before running new macros, and verify the exact names of the worksheets you intend to exclude from the VBA calculations.

Solution 1Recommended

Implement Dynamic VBA for Section Totals

Use a comprehensive VBA macro to loop through worksheets, clear old totals, calculate revenue and subtotals, and dynamically generate grand totals.

Open the VBA Editor

Press Alt + F11 to open the Microsoft Visual Basic for Applications window, then click Insert > Module to create a new script area.

Define the Loop and Exclusions

Write a 'For Each' loop to iterate through all worksheets, using an 'If' statement to exclude specific sheets (e.g., If ws.Name <> "Summary" Then).

Clear Existing Totals

Use dynamic range finding to identify and clear old totals before generating new ones, ensuring data does not stack incorrectly.

Calculate Gross Profit

Write subtotal calculation loops for the revenue and expense sections, then calculate Gross Profit by dynamically subtracting total expenses from total revenue.

Solution 2

Preserve Formatting When Filling Formulas

Replace the standard FillRight method with direct formula assignment to prevent VBA from overwriting cell formatting.

Solution 3

Test References Using a Sample Workbook

Validate the VBA code on a sample file before deploying it to your production workbook to avoid accidental data corruption.

Powerful VBA Support in WPS Office

Automate Financial Totals using VBA in WPS Spreadsheets

WPS Spreadsheets provides robust support for Excel VBA macros, allowing you to seamlessly calculate section subtotals, grand totals, and profit/loss. It fully supports standard VBA syntax, meaning your dynamic ranges and formula assignments will work flawlessly.

1. Open Your Workbook: Launch WPS Spreadsheets and open your financial workbook.
2. Access the VBA Editor: Go to the Developer tab on the ribbon and click on 'Visual Basic' to open the built-in VBA Editor.
3. Insert Your Macro: Paste your customized macro code for calculating section totals and gross profit into a new Module.
4. Run the Macro: Click the Run button or press F5 to execute the script and dynamically update your financial sheets.

Fully compatible with Microsoft Excel macro (.xlsm) formats.Advanced VBA editor to seamlessly write, test, and debug financial macros.Lightweight software that processes heavy financial calculations without lagging.Free to use with a highly familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the FillRight VBA method change my cell formatting?

The FillRight method in Excel VBA copies the entire cell object, which includes both its formula and its format properties (like background color and borders). To avoid this, use direct formula assignment like `Range.Formula = SourceCell.Formula` over a resized range instead.

How can I exclude specific worksheets from a VBA loop?

You can use an If statement coupled with Select Case or check against an array of excluded names (e.g., `If ws.Name <> "Summary" And ws.Name <> "Data" Then`) inside your `For Each ws In ThisWorkbook.Worksheets` loop.

Can I run Excel VBA code in WPS Office?

Yes, WPS Office supports running and editing VBA macros. You simply need to enable the Developer tab in WPS Spreadsheets to access the VBA Editor and execute standard Excel macro scripts natively.

How do I dynamically find the last row to calculate a grand total?

You can find the last used row in a column using VBA by utilizing the End(xlUp) method. For example, `LastRow = Cells(Rows.Count, "A").End(xlUp).Row` will return the row number of the last non-empty cell in column A.

Excel & Data Problems