In VBA, possible ot subtotal without selecting?

[Celt]

As always, thanks in advance for any guidance offered!!!

I have a macro that works just fine up until this section. Excel Gur
Tom Ogilivy provided me with some code that allowed me to set "rngsort
and rngsubtotal" once and use them on all of my worksheets (thank
Tom!!). What makes this tricky (at least for me) is that my sort an
subtotal parameters are not the same on each sheet, however the range
are. This is wht I have done so far:

For Each wS In Worksheets
With wS
Set rngsort = Range(Cells(4, 1), Cells(Rows.Count, 7).End(xlDown))
Set rngsubtotal = Range(Cells(3, 1), Cells(Rows.Count, 7).End(xlDown))
End With
If wS.Name = "Contribs" Then
rngsort.sort Key1:=Range("G4"), Order1:=xlAscending, Header:=xlGuess
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
_
DataOption1:=xlSortNormal
rngsubtotal.subtotal GroupBy:=7, Function:=xlSum, TotalList:=Array(9)
_
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
End If
If wS.Name = "Workplace" Then
rngsort.sort Key1:=Range("E4"), Order1:=xlAscending, Header:=xlGuess
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
_
DataOption1:=xlSortNormal
rngsubtotal.subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(9)
_
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
End If
' I so some sorting and subtotaling on other sheets ......

Next wS

The macro works fine up until my first use of the range rngsubtotal (i
the first If statement).

Do I have to activate the range or worksheet in order to subtotal? O
is it possible to do so without actually selecting anything (I a
trying to get out of the habit of selecting stuff, but still learning)
Maybe my code is worded incorrectly? (also, is there is a mor
efficient way to do what I am trying to do.... I'm all ears).

Thanks All

 

[Guest]

the way you have written it, I would think you would need:

For Each wS In Worksheets
With wS
.Activate
Set rngsort = Range(Cells(4, 1), Cells(Rows.Count, 7).End(xlDown))
Set rngsubtotal = Range(Cells(3, 1), Cells(Rows.Count, 7).End(xlDown))
End With


so that the sheet you are working on is the active sheet.

 

[Celt]

Thanks Tom! I'll give that a shot.

One other quick question....with my coding. Is it long winded? I
there a better way more efficient approach I should try?

Thanks again for all your guidance

 

[Celt]

Actually...that didn't work.

At the first instance of :

rngsubtotal.subtotal GroupBy:=7, Function:=xlSum, TotalList:=Array(9)
_
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2

I get a [Run time error '1004' : Subtotal Method of Range clas
failed].

Is this telling me that the macro can't tell what it is supposed t
subtotal

 

[Guest]

Kind of - From what I have seen in your code

You have told it to sum the 9th column and your range only has 7 columns.

so probably the better explanation is that the macro doesn't think you know
what you are doing <g>.

Perhaps you want to add after these lines:
Set rngsort = Range(Cells(4, 1), Cells(Rows.Count, 7).End(xlDown))
Set rngsubtotal = Range(Cells(3, 1), Cells(Rows.Count, 7).End(xlDown))
set rngsort = rngsort.Resize(,9)
set rngsubtolal = rngsubtotal.Resize(,9)

or change the subtotal command to subtotal on a column 7 or lower. You know
your data better than me.

 

[Celt]

Thanks very much Tom!

You are totally right. My data has 10 columns. I didn't even catc
(until you pointed it out) that my range only extends 7. I can see wh
that might create a problem.

I think you were correct when you said the macro doesn't think I kno
what I am doing!

I really appreciate your help

 

[Dave Peterson]

Macros should do what you want--not what you code!

Heck, all of life should be like this.

 

[Celt]

Ain't that the truth Dave!