Same Range on different worksheets

[sgrech]

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

[Fountainhead]

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?

Please Login or Register  to view this content.

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

[sgrech]

Have you thought about making each range a public variable?

Please Login or Register  to view this content.

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

[Ron de Bruin]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[sgrech]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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

[Ron de Bruin]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> >
>> > 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
>