Is it possible to Set a range so that I can use it on any worksheet in
my workbook?

Currently, I am selecting each sheet and then setting the range. What
I would like to do is set it once and then be able to call it from any
worksheet.

Here is my range code:
Dim rngsort As Range, rngsubtotal As Range

' I then set these ranges below on each sheet I am working on...

Set rngsort = Range(Cells(4, 1), Cells(Rows.Count, 7).End(xlDown))
Set rngsubtotal = Range(Cells(3, 1), Cells(Rows.Count, 7).End(xlDown))

Thanks for any advice offered!!!


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

You can do this even for open workbooks as well as worksheets:

Set r1 = Workbooks("Book1").Worksheets("Sheet1").Cells(1,2)

--
Gary's Student


"Celt" wrote:


Is it possible to Set a range so that I can use it on any worksheet in
my workbook?

Currently, I am selecting each sheet and then setting the range. What
I would like to do is set it once and then be able to call it from any
worksheet.

Here is my range code:
Dim rngsort As Range, rngsubtotal As Range

' I then set these ranges below on each sheet I am working on...

Set rngsort = Range(Cells(4, 1), Cells(Rows.Count, 7).End(xlDown))
Set rngsubtotal = Range(Cells(3, 1), Cells(Rows.Count, 7).End(xlDown))

Thanks for any advice offered!!!


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

Thanks Gary's Student!!

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

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

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

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

Thanks again for 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=55112

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

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

Thanks very much Tom & Gary's Student!!!

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