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