In VBA, possible ot subtotal without selecting?
 

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

--
Cel
-----------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...fo&userid=1941
View this thread: http://www.excelforum.com/showthread.php?threadid=55154

In VBA, possible ot subtotal without selecting?
 

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

In VBA, possible ot subtotal without selecting?
 

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

--
Cel
-----------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...fo&userid=1941
View this thread: http://www.excelforum.com/showthread.php?threadid=55154

In VBA, possible ot subtotal without selecting?
 

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

--
Cel
-----------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...fo&userid=1941
View this thread: http://www.excelforum.com/showthread.php?threadid=55154

In VBA, possible ot subtotal without selecting?
 

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

In VBA, possible ot subtotal without selecting?
 

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

--
Cel
-----------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...fo&userid=1941
View this thread: http://www.excelforum.com/showthread.php?threadid=55154

In VBA, possible ot subtotal without selecting?
 

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

In VBA, possible ot subtotal without selecting?
 

Ain't that the truth Dave!


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=551544