Subtotal Macro Help


G

Guest

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
 
Ad

Advertisements

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)
 
Ad

Advertisements

G

Guest

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

Similar Threads

Subtotal Formatting 2
Subtotal macro in each worksheet 13
Rounding Subtotals to 2 places 1
Subtotal by VBA 5
Issue with nested data subtotals 3
Subtotal function 2
Excel Subtotals 2
macro excel subtotal in subtotal 2

Top