If you try to do something like:

Sub Macro1()
Dim r As Range
Set r = Range("A1:B2")
r.Select
Sheets("Sheet2").Select
ActiveSheet.r.Select
End Sub

In order to select the same block of cells on Sheet2 that were selected on
Sheet1, the macro fails.


If you want to save the address of a block of cells and select it on various
sheets, the trick is to use a string rather than a range:

Sub Macro1()
Dim r As Range, s As String
s = "A1:B2"
Set r = Range(s)
r.Select
Sheets("Sheet2").Select
ActiveSheet.Range(s).Select
End Sub

the variable s holds the information and can be used on any sheet.

--
Gary's Student


"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