Dim sh as Worksheet
for each sh in thisWorkbook.Worksheets
with sh
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
' now work with rngsort adn rngsubtotal for "sh"
' i.e. finish processing "sh" then move on and do the
' next "sh". Your variables are reset for each "sh"
' as it is processed
Next sh

--
Regards,
Tom Ogilvy




"Celt" wrote:


Thanks Gary's Student!!

Bear with me here for a sec.....

In the example you provided, since you specified "Sheet1" does that
mean the range applies only to Sheet1?

Let's say my workbook is called "Fred.xls" and my worksheets are Sheet1
- Sheet3.

What would my set range need to look like so that I could use rngsort
or rngsubtotal on every sheet?

Thanks again for your help!


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