Subtotal Macro Help

M

masterbaker

I have some VB that SHOULD subtotal each tab that is not equal to "Source
Data" but when I run the Macro it subtotals multiple times on the same tab
instead of moving to the next tab after subtotaling. Can anyone help me
straighten out my For statement? Here's the Macro:

For Each ws In ActiveWorkbook.Sheets
If ws.Name <> "Source Data" Then _
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(11, 12,
13 _
, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23), Replace:=True,
PageBreaks:=False, _
SummaryBelowData:=True
Next ws
 
B

Bob Phillips

For Each ws In ActiveWorkbook.Sheets
If ws.Name <> "Source Data" Then
ws.Activate
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, _
TotalList:= Array(11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
21, 22, 23), _
Replace:=True, _
PageBreaks:=False, _
SummaryBelowData:=True
End If
Next ws

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
M

masterbaker

Perfect Bob, Thanks. I just needed to activiate the sheet and end my if
statement.

Easy fix to a stupid error....Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top