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 Guru Tom Ogilivy provided me with some code that allowed me to set "rngsort" and rngsubtotal" once and use them on all of my worksheets (thanks Tom!!). What makes this tricky (at least for me) is that my sort and subtotal parameters are not the same on each sheet, however the ranges 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 (in the first If statement).

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

Thanks All!

[Tom Ogilvy]

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.

--
Regards,
Tom Ogilvy


"Celt" wrote:

>
> As always, thanks in advance for any guidance offered!!!
>
> I have a macro that works just fine up until this section. Excel Guru
> Tom Ogilivy provided me with some code that allowed me to set "rngsort"
> and rngsubtotal" once and use them on all of my worksheets (thanks
> Tom!!). What makes this tricky (at least for me) is that my sort and
> subtotal parameters are not the same on each sheet, however the ranges
> 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 (in
> the first If statement).
>
> Do I have to activate the range or worksheet in order to subtotal? Or
> is it possible to do so without actually selecting anything (I am
> trying to get out of the habit of selecting stuff, but still learning).
> Maybe my code is worded incorrectly? (also, is there is a more
> efficient way to do what I am trying to do.... I'm all ears).
>
> Thanks All!
>
>
> --
> Celt
> ------------------------------------------------------------------------
> Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
> View this thread: http://www.excelforum.com/showthread...hreadid=551544
>
>

[Celt]

Thanks Tom! I'll give that a shot.

One other quick question....with my coding. Is it long winded? Is 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 class failed].

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

[Tom Ogilvy]

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.

--
Regards,
Tom Ogilvy



"Celt" wrote:

>
> 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 class
> failed].
>
> Is this telling me that the macro can't tell what it is supposed to
> subtotal?
>
>
> --
> Celt
> ------------------------------------------------------------------------
> Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
> View this thread: http://www.excelforum.com/showthread...hreadid=551544
>
>

[Celt]

Thanks very much Tom!

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

I think you were correct when you said the macro doesn't think I know 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 wrote:
>
> Thanks very much Tom!
>
> You are totally right. My data has 10 columns. I didn't even catch
> (until you pointed it out) that my range only extends 7. I can see why
> that might create a problem.
>
> I think you were correct when you said the macro doesn't think I know
> what I am doing!
>
> I really appreciate your help!
>
> --
> Celt
> ------------------------------------------------------------------------
> Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
> View this thread: http://www.excelforum.com/showthread...hreadid=551544

--

Dave Peterson

[Celt]

Ain't that the truth Dave!