Hi I have a workbook with multiple worksheets. For each worksheet I
need to define a range of cells (called for example TestRange), but the
range of cells to be defined with this name on each worksheet can
differ. I have tried to define a cell range with the same name on
mulitple worksheets but it doesn't work as excel just returns you to
the first worksheet where the range was defined. However I did however
notice that if I make a copy of worksheet with the defined cell range
then it is possible to have multiple worksheets with the same named
cell range.

However all of my spreadsheets are already set-up so I don't want to
have to set them up again from scratch again as this will take me a lot
of time.

Is there an alternative way to achieving my objective with having to
make copies of of the original worksheet with the defined cell range?

Any help would be much appreciated.

Cheers
Simon


--
sgrech
------------------------------------------------------------------------
sgrech's Profile: http://www.excelforum.com/member.php...o&userid=14501
View this thread: http://www.excelforum.com/showthread...hreadid=526300

sgrech Wrote:
Hi I have a workbook with multiple worksheets. For each worksheet I
need to define a range of cells (called for example TestRange), but the
range of cells to be defined with this name on each worksheet can
differ. I have tried to define a cell range with the same name on
mulitple worksheets but it doesn't work as excel just returns you to
the first worksheet where the range was defined. However I did however
notice that if I make a copy of worksheet with the defined cell range
then it is possible to have multiple worksheets with the same named
cell range.

However all of my spreadsheets are already set-up so I don't want to
have to set them up again from scratch again as this will take me a lot
of time.

Is there an alternative way to achieving my objective with having to
make copies of of the original worksheet with the defined cell range?

Any help would be much appreciated.

Cheers
Simon

Have you thought about making each range a public variable?


Code:
--------------------
Public r1 As Range, r2 As Range
Set r1 = Range("A1:B2")
Set r2 = Range("C3:D4")
--------------------


Now you can use r1 for the first sheet, r2 for the second sheet, ad
infinitum.


--
Fountainhead
------------------------------------------------------------------------
Fountainhead's Profile: http://www.excelforum.com/member.php...o&userid=32825
View this thread: http://www.excelforum.com/showthread...hreadid=526300

Fountainhead Wrote:
Have you thought about making each range a public variable?


Code:
--------------------
Public r1 As Range, r2 As Range
Set r1 = Range("A1:B2")
Set r2 = Range("C3:D4")
--------------------


Now you can use r1 for the first sheet, r2 for the second sheet, ad
infinitum.

This really won't work for me, I really need to have the same name for
the range on each worksheet. As said previously I know i can achieve
this by making copies of the original worksheet but this in itself will
cause me additional set-up work.

Any other ideas from anyone.

Simon


--
sgrech
------------------------------------------------------------------------
sgrech's Profile: http://www.excelforum.com/member.php...o&userid=14501
View this thread: http://www.excelforum.com/showthread...hreadid=526300

Try this

Sub Give_name_on_all_sheets()
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Worksheets
Sh.Range("A5").Name = Sh.Name & "!yourname"
Next
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"sgrech" wrote in message
...

Fountainhead Wrote:
Have you thought about making each range a public variable?


Code:
--------------------
Public r1 As Range, r2 As Range
Set r1 = Range("A1:B2")
Set r2 = Range("C3:D4")
--------------------


Now you can use r1 for the first sheet, r2 for the second sheet, ad
infinitum.

This really won't work for me, I really need to have the same name for
the range on each worksheet. As said previously I know i can achieve
this by making copies of the original worksheet but this in itself will
cause me additional set-up work.

Any other ideas from anyone.

Simon


--
sgrech
------------------------------------------------------------------------
sgrech's Profile: http://www.excelforum.com/member.php...o&userid=14501
View this thread: http://www.excelforum.com/showthread...hreadid=526300

Ron de Bruin Wrote:
Try this

Sub Give_name_on_all_sheets()
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Worksheets
Sh.Range("A5").Name = Sh.Name & "!yourname"
Next
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"sgrech" wrote in
message
...

Fountainhead Wrote:
Have you thought about making each range a public variable?


Code:
--------------------
Public r1 As Range, r2 As Range
Set r1 = Range("A1:B2")
Set r2 = Range("C3:D4")
--------------------


Now you can use r1 for the first sheet, r2 for the second sheet, ad
infinitum.

This really won't work for me, I really need to have the same name
for
the range on each worksheet. As said previously I know i can
achieve
this by making copies of the original worksheet but this in itself
will
cause me additional set-up work.

Any other ideas from anyone.

Simon


--
sgrech

------------------------------------------------------------------------
sgrech's Profile:
http://www.excelforum.com/member.php...o&userid=14501
View this thread:
http://www.excelforum.com/showthread...hreadid=526300



Sorry Ron,
But this also won't work for me. I have worksheets within the workbook
which I don't wont this range to applied to. Much more importantly
though the range is not exactly the same cells on each worksheet so I
don't think I can apply the range via a macro.
Simon


--
sgrech
------------------------------------------------------------------------
sgrech's Profile: http://www.excelforum.com/member.php...o&userid=14501
View this thread: http://www.excelforum.com/showthread...hreadid=526300

You can loop through a array
For Each sh In Sheets(Array("Sheet1", "Sheet3"))

Do you know the range of each sheet ?



--
Regards Ron de Bruin
http://www.rondebruin.nl


"sgrech" wrote in message
...

Ron de Bruin Wrote:
Try this

Sub Give_name_on_all_sheets()
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Worksheets
Sh.Range("A5").Name = Sh.Name & "!yourname"
Next
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"sgrech" wrote in
message
...

Fountainhead Wrote:
Have you thought about making each range a public variable?


Code:
--------------------
Public r1 As Range, r2 As Range
Set r1 = Range("A1:B2")
Set r2 = Range("C3:D4")
--------------------


Now you can use r1 for the first sheet, r2 for the second sheet, ad
infinitum.

This really won't work for me, I really need to have the same name
for
the range on each worksheet. As said previously I know i can
achieve
this by making copies of the original worksheet but this in itself
will
cause me additional set-up work.

Any other ideas from anyone.

Simon


--
sgrech

------------------------------------------------------------------------
sgrech's Profile:
http://www.excelforum.com/member.php...o&userid=14501
View this thread:
http://www.excelforum.com/showthread...hreadid=526300



Sorry Ron,
But this also won't work for me. I have worksheets within the workbook
which I don't wont this range to applied to. Much more importantly
though the range is not exactly the same cells on each worksheet so I
don't think I can apply the range via a macro.
Simon


--
sgrech
------------------------------------------------------------------------
sgrech's Profile: http://www.excelforum.com/member.php...o&userid=14501
View this thread: http://www.excelforum.com/showthread...hreadid=526300