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