Macro to insert named range in each sheet

[rbanks]

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

 

[idyllicabyss]

try;

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

 

[idyllicabyss]

Sorry, should have been;

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

 

[Otto Moehrbach]

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]

I have 50 - 60 sheets

 

[Otto Moehrbach]

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

 

[Tom Ogilvy]

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

 

[rbanks]

Tom, Otto,

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

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

 

[Otto Moehrbach]

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

 

[rbanks]

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

Thanks again.