Loop existing subtotal and formatting macro

cyrfin

New Member
Joined
Mar 14, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I have a macro that I created to subtotal and format the sheet in a specific way. I'm trying to create a loop so that this macro will run on all remaining tabs in my workbook, but it's only completing this on the first tab and nothing happens with the rest. Can someone help to understand what's wrong with my code? The number of tabs in the workbook can vary, however, the "All Stores" tab is consistent and is the only tab that should be excluded. Here's what I have so far:

Sub MovethroughWB() 'Excel VBA to exclude All Stores from the loop.
Dim ws As Worksheet

For Each ws In Sheets
If ws.Name <> "All Stores" Then
Rows("2:2").Select
ActiveWindow.FreezePanes = True

Cells(1, 1).Select

Selection.Subtotal GroupBy:=10, Function:=xlSum, TotalList:=Array(12), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
ActiveWindow.SmallScroll Down:=-3
End If
Next ws
End Sub

The indented rows are the first macro I wrote, and when I run that macro it works great. I then tried to create the loop so that the same macro could be run on the rest of the worksheets in the workbook. However, each time I run it, it still only runs the macro on the sheet I'm on. I'm new to looping, so any help you can provide would be greatly appreciated. What I've done thus far is simply what I found on other sites trying to learn how to loop. Clearly I'm missing something.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,149
Office Version
  1. 365
Platform
  1. Windows
Give this a shot.

VBA Code:
Sub MovethroughWB() 'Excel VBA to exclude All Stores from the loop.
Dim ws As Worksheet

For Each ws In Sheets
    If ws.Name <> "All Stores" Then
        ws.Activate
        ws.Rows("2:2").Select
        ActiveWindow.FreezePanes = True
        ws.Cells(1, 1).Select
        Selection.Subtotal GroupBy:=10, Function:=xlSum, TotalList:=Array(12), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
        ws.Outline.ShowLevels RowLevels:=2
        ActiveWindow.SmallScroll Down:=-3
    End If
Next ws
End Sub
 
Solution

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,149
Office Version
  1. 365
Platform
  1. Windows
───────────────────░█▓▓▓█░▇▆▅▄▃▂
──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂
─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂
 

Watch MrExcel Video

Forum statistics

Threads
1,129,903
Messages
5,638,899
Members
417,058
Latest member
BRYCEPIETROWIAK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top