Macro to insert named range in each sheet
 

I'm constantly creating workbooks (thru Essbasse Cascade function) that
have 50 or more sheets.

I'd like a quick macro to defined a named range on each sheet, using
the tab name as the range name.

Example

I have 3 sheets named "Dallas", "Houston", and "Austin". Id like to
define the range A1:Z200 in each sheet, and name that range "Dallas",
"Houston", and "Austin".

No to bore you with details, but this is important because I use these
named ranges in my Essbase retrieve macros.

Thanks


--
rbanks
------------------------------------------------------------------------
rbanks's Profile: http://www.excelforum.com/member.php...fo&userid=2944
View this thread: http://www.excelforum.com/showthread...hreadid=526217

Macro to insert named range in each sheet
 

try;

For Each sh In ThisWorkbook.Sheets
ActiveWorkbook.Names.Add Name:="Name", RefersToR1C1:="=" & _
sh.Name & "!R1C1:R200C26"
Next sh

Macro to insert named range in each sheet
 

Sorry, should have been;

ActiveWorkbook.Names.Add Name:= sh.Name, RefersToR1C1:="=" & _
sh.Name & "!R1C1:R200C26"

Macro to insert named range in each sheet
 

If those 3 sheets are all the sheets you have, use the following macro. If
you want to do this with each sheet in a file or with each sheet except this
and that sheets, post back. HTH Otto
Sub NameRngs()
Dim sh As Worksheet
For Each sh In Sheets(Array("Dallas", "Houston", "Austin"))
With sh
.Range("A1:Z200").Name = sh.Name
End With
Next sh
End Sub

"rbanks" wrote in
message ...

I'm constantly creating workbooks (thru Essbasse Cascade function) that
have 50 or more sheets.

I'd like a quick macro to defined a named range on each sheet, using
the tab name as the range name.

Example

I have 3 sheets named "Dallas", "Houston", and "Austin". Id like to
define the range A1:Z200 in each sheet, and name that range "Dallas",
"Houston", and "Austin".

No to bore you with details, but this is important because I use these
named ranges in my Essbase retrieve macros.

Thanks


--
rbanks
------------------------------------------------------------------------
rbanks's Profile:
http://www.excelforum.com/member.php...fo&userid=2944
View this thread: http://www.excelforum.com/showthread...hreadid=526217

Macro to insert named range in each sheet
 

I have 50 - 60 sheets


--
rbanks
------------------------------------------------------------------------
rbanks's Profile: http://www.excelforum.com/member.php...fo&userid=2944
View this thread: http://www.excelforum.com/showthread...hreadid=526217

Macro to insert named range in each sheet
 

I can write that macro but you have to tell me if you want this done to
EVERY sheet in the file or you want to exempt a few sheets. If you want
this done to EVERY sheet in the file use the following macro. HTH Otto
Sub NameRngs()
Dim Sh As Worksheet
For Each Sh In ActiveWorkbook.Worksheets
With Sh
.Range("A1:Z200").Name = Sh.Name
End With
Next Sh
End Sub
"rbanks" wrote in
message ...

I have 50 - 60 sheets


--
rbanks
------------------------------------------------------------------------
rbanks's Profile:
http://www.excelforum.com/member.php...fo&userid=2944
View this thread: http://www.excelforum.com/showthread...hreadid=526217

Macro to insert named range in each sheet
 

Sub addnames()
Dim v as Variant, i as long
dim sh as Worksheet
v = array("Dallas", "Austin", "Houston")
for i = lbound(v) to ubound(v)
set sh = worksheets(v(i))
sh.Range("A1:Z200").Name = v(i)
Next
End Sub

--
Regards,
Tom Ogilvy


"rbanks" wrote:


I'm constantly creating workbooks (thru Essbasse Cascade function) that
have 50 or more sheets.

I'd like a quick macro to defined a named range on each sheet, using
the tab name as the range name.

Example

I have 3 sheets named "Dallas", "Houston", and "Austin". Id like to
define the range A1:Z200 in each sheet, and name that range "Dallas",
"Houston", and "Austin".

No to bore you with details, but this is important because I use these
named ranges in my Essbase retrieve macros.

Thanks


--
rbanks
------------------------------------------------------------------------
rbanks's Profile: http://www.excelforum.com/member.php...fo&userid=2944
View this thread: http://www.excelforum.com/showthread...hreadid=526217

Macro to insert named range in each sheet
 

Tom, Otto,

I have tried both of you suggestions, and they both error out.:eek:

Tom's

Sub addnames()
Dim v As Variant, i As Long
Dim sh As Worksheet
v = Array("Domestic CSCs", "Toronto CSC", "Vancouver CSC", "Winnipeg
CSC", _
"Calgary CSC", "Montreal CSC", "Halifax CSC", "Canadian DPS",
"Anchorage CSC", _
"Atlanta", "Boston CSC", "Chicago CSC", "Cincinnati CSC",
"Denver CSC", _
"Houston CSC", "Kansas City -CSC", "Los Angeles CSC", "Memphis
CSC", _
"Minneapolis CSC", "San Jose CSC", "Philadelphia CSC",
"Phoenix-CSC", _
"Pittsburgh-CSC", "Pontiac-CSC", "Portland CSC")
For i = LBound(v) To UBound(v)
Set sh = Worksheets(v(i))
sh.Range("A1:N256").Name = v(i)
Next
End Sub

Otto's

Sub NameRngs()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
With sh
Range("A1:N253").Name = sh.Name
End With
Next sh
End Sub


--
rbanks
------------------------------------------------------------------------
rbanks's Profile: http://www.excelforum.com/member.php...fo&userid=2944
View this thread: http://www.excelforum.com/showthread...hreadid=526217

Macro to insert named range in each sheet
 

What is the error? When you click on the "Debug" in the error window, what
line of code is highlighted? Otto
"rbanks" wrote in
message ...

Tom, Otto,

I have tried both of you suggestions, and they both error out.:eek:

Tom's

Sub addnames()
Dim v As Variant, i As Long
Dim sh As Worksheet
v = Array("Domestic CSCs", "Toronto CSC", "Vancouver CSC", "Winnipeg
CSC", _
"Calgary CSC", "Montreal CSC", "Halifax CSC", "Canadian DPS",
"Anchorage CSC", _
"Atlanta", "Boston CSC", "Chicago CSC", "Cincinnati CSC",
"Denver CSC", _
"Houston CSC", "Kansas City -CSC", "Los Angeles CSC", "Memphis
CSC", _
"Minneapolis CSC", "San Jose CSC", "Philadelphia CSC",
"Phoenix-CSC", _
"Pittsburgh-CSC", "Pontiac-CSC", "Portland CSC")
For i = LBound(v) To UBound(v)
Set sh = Worksheets(v(i))
sh.Range("A1:N256").Name = v(i)
Next
End Sub

Otto's

Sub NameRngs()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
With sh
Range("A1:N253").Name = sh.Name
End With
Next sh
End Sub


--
rbanks
------------------------------------------------------------------------
rbanks's Profile:
http://www.excelforum.com/member.php...fo&userid=2944
View this thread: http://www.excelforum.com/showthread...hreadid=526217

Macro to insert named range in each sheet
 

Thanks guys. It was my fault. Both suggestions will work, one I
removed the spaces from my tab names.:(

Thanks again.


--
rbanks
------------------------------------------------------------------------
rbanks's Profile: http://www.excelforum.com/member.php...fo&userid=2944
View this thread: http://www.excelforum.com/showthread...hreadid=526217