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

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

I have 50 - 60 sheets

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

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
>
>

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

What is the error? When you click on the "Debug" in the error window, what
line of code is highlighted? Otto
"rbanks" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> rbanks
> ------------------------------------------------------------------------
> rbanks's Profile:
> http://www.excelforum.com/member.php...fo&userid=2944
> View this thread: http://www.excelforum.com/showthread...hreadid=526217
>

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

Thanks again.