VBA & Macro Problems

Run an Excel VBA Macro on Multiple Summary Worksheets

Anum MushtaqAnum Mushtaq Nov 14, 2026 869 views

Question details

Run the same Excel VBA processing on both Summary 1 and Summary 2.

Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
A macro must apply the same formatting, spacing, calculations, autofit, and freeze-pane operations to two summary worksheets.
Observed behavior
The existing macro processes only the worksheet named Summary 1.
Before you start

Confirm that both worksheets are named exactly Summary 1 and Summary 2 and that the macro is stored in the workbook containing them. Identify the existing formatting and calculation code so you can move it into the worksheet loop.

Solution 1Recommended

Loop through both summary sheets and qualify worksheet references

Use a worksheet variable to run the existing operations on each named sheet, and activate each sheet before setting freeze panes.

1
Open the macro

In Excel, open the Visual Basic Editor with Alt+F11 and select the module containing your existing macro.

2
Add a worksheet loop

Adapt your macro to use this structure: Sub ProcessSummarySheets() Dim ws As Worksheet ThisWorkbook.Activate For Each ws In ThisWorkbook.Worksheets(Array("Summary 1", "Summary 2")) ' Move your existing formatting, spacing, and calculation code here. ' Qualify its worksheet references with ws. ws.Calculate ws.Columns.AutoFit ' Example: freeze the first row. ws.Activate ActiveWindow.FreezePanes = False ws.Range("A2").Select ActiveWindow.FreezePanes = True Next ws End Sub

3
Update the existing code

Inside the For Each loop, replace references to the fixed Summary 1 sheet with ws. Qualify every Range, Cells, Rows, and Columns reference, including nested references; for example, use ws.Range(ws.Cells(1, 1), ws.Cells(5, 2)). Remove duplicate calculation or autofit lines if your existing code already performs them.

4
Set the intended freeze point and run

Replace A2 with the cell below and to the right of the rows and columns you want frozen. Run the macro from the Visual Basic Editor, then check formatting, calculations, column widths, and freeze panes on both summary sheets.

Freeze panes uses the active window: The example freezes only the first row. FreezePanes is a window property, so activate each worksheet before selecting the freeze point and setting it.
Free Microsoft Office alternative

Work with summary spreadsheets in WPS Office

WPS Office is a free, lightweight Office alternative with a familiar interface for working with spreadsheets. Open compatible Excel files to continue your work, but check macro support before migrating a VBA-dependent workbook; WPS does not fix Excel VBA behavior.

Review and format summary worksheets in a familiar spreadsheet interfaceCompatible with Microsoft Excel .xlsx files for easier migrationFree, lightweight suite for everyday document and spreadsheet work
microsoft office alternative - wps office

Frequently Asked Questions

Why does the macro still change only Summary 1?

Check the code inside the For Each loop for hard-coded references such as Worksheets("Summary 1"). Replace them with ws, and qualify Range, Cells, Rows, and Columns references so they point to the current worksheet.

How do I freeze the first column instead of the first row?

After activating the worksheet and clearing FreezePanes, select ws.Range("B1") and set ActiveWindow.FreezePanes to True. To freeze both the first row and first column, select B2 instead.

Can I add more worksheets to the loop?

Yes. Add their exact names to the Array in ThisWorkbook.Worksheets, for example Array("Summary 1", "Summary 2", "Summary 3"). Each named worksheet must exist in that workbook.

What happens if a summary sheet has a different name?

The worksheet lookup will fail if a named sheet does not exist. Check the sheet tabs and update the names in the Array to match them.