MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Range NAme Macro


Posted by Douglas on January 16, 2002 8:40 AM

Hi,

I'd like to write a macro which will scroll through every sheet in a workbook and create a named range on each page.

The location of the range would be the same on each page (A4:N26) and the name of the range would be the text contained in cell A2 of each sheet.

Can someone help please?

Thanks


Posted by Russell Hauf on January 16, 2002 9:04 AM

Try this:


Sub DefineRanges()

Dim sht As Worksheet

For Each sht In ActiveWorkbook.Worksheets
sht.Select
sht.Names.Add Name:=Range("A2").Text, RefersTo:=sht.Range("A4:N26")
Next sht

End Sub

Hope this helps,

Russell

Posted by Douglas on January 16, 2002 9:20 AM

Russell

Thanks for responding!

When I try to run this I get an error and the code stops on the line:
sht.Names.Add Name:=Range("A2").Text, RefersTo:=sht.Range("A4:N26")

Any suggestions??

Dim sht As Worksheet For Each sht In ActiveWorkbook.Worksheets sht.Select sht.Names.Add Name:=Range("A2").Text, RefersTo:=sht.Range("A4:N26") Next sht


Posted by Nate Oliver on January 16, 2002 9:36 AM

Try this:


Sub The_Namer()

Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
thename = Range("a2")
On Error GoTo errorhandler
ActiveWorkbook.Names.Add Name:=thename, RefersTo:=Range("A4:N26")
Next sht
Exit Sub

errorhandler:
MsgBox ("Cell a2 must not include spaces")
End Sub


Good luck and cheers!

Nate

Posted by Russell Hauf on January 16, 2002 9:38 AM

Nate's right - range names cannot have spaces in them (see Excel help for more info on range names).

rh

Posted by Russell Hauf on January 16, 2002 9:39 AM

Also...

Also, you must select each sheet as you go through, or you will only get one range.

-rh Nate's right - range names cannot have spaces in them (see Excel help for more info on range names). rh : Try this

Posted by Nate Oliver on January 16, 2002 9:46 AM

Re: Also...

Right indeed Russell, I should test my stuff! I believe this will work:

Sub The_Namer()

Dim sht As Worksheet
Worksheets(1).Select
For Each sht In ActiveWorkbook.Worksheets
sht.Select
thename = Range("a2")
On Error GoTo errorhandler
ActiveWorkbook.Names.Add Name:=thename, RefersTo:=Range("A4:N26")
Next sht
Exit Sub

errorhandler:
MsgBox ("Cell a2 must not include spaces")
End Sub


Cheers! Nate

Posted by Douglas on January 16, 2002 9:55 AM

FANTASTIC! - Thanks guys

Right indeed Russell, I should test my stuff! I believe this will work: Sub The_Namer() Dim sht As Worksheet

Posted by Douglas on January 16, 2002 10:27 AM

FANTASTIC! - Thanks guys

Right indeed Russell, I should test my stuff! I believe this will work: Sub The_Namer() Dim sht As Worksheet

Posted by Nate Oliver on January 16, 2002 10:27 AM

Re: FANTASTIC! Not Yet, but the following is getting closer!

Might as well make this something Mom would be proud of at this point. The following will allow you to have name's with spaces in them and traps the error in the event that the cell is blank.

Cheers! Nate

Sub The_Namer2()

Application.ScreenUpdating = False
Dim sht As Worksheet
Dim thename As String
Worksheets(1).Select
For Each sht In ActiveWorkbook.Worksheets
sht.Select
thename = WorksheetFunction.Substitute(Range("a2"), " ", "_")
On Error GoTo errorhandler
ActiveWorkbook.Names.Add Name:=thename, RefersTo:=Range("A4:N26")
Range("iu98").ClearContents
Range("a1").Select
Next sht
Application.ScreenUpdating = True
Exit Sub

errorhandler:
Application.ScreenUpdating = True

MsgBox ("Cell A2's contents are empty, cannot name a range as such")

End Sub

Posted by Nate Oliver on January 16, 2002 10:29 AM

Re: FANTASTIC! Not Yet, but the following is getting closer!

Whoops, you can remove:

Range("iu98").ClearContents

I left it in inadvertantly, it's of no use.

Nate

MsgBox ("Cell A2's contents are empty, cannot name a range as such") End Sub

Posted by Russell Hauf on January 16, 2002 3:13 PM

Are you one-upping me?! :)

Here's my "Mom" version. :)


Sub DefineRanges()

On Error GoTo HandleErr

Dim sht As Worksheet
Dim strName As String
Dim xlsCurr As Worksheet

Application.ScreenUpdating = False

' Capture the current sheet
Set xlsCurr = ActiveSheet

For Each sht In ActiveWorkbook.Worksheets
sht.Select
strName = Range("A2").Text
sht.Names.Add Name:=strName, RefersTo:=sht.Range("A4:N26")
Next sht

ExitHere:
xlsCurr.Select
Application.ScreenUpdating = True
Exit Sub

HandleErr:
Select Case Err.Number
Case 1004
If Trim(Err.Description) Like "That name is not valid." Then
'If you have Excel 2000 or greater, then uncomment
'the next line and delete the Mid(strName...) line.

'strName = Replace(strName, " ", "_")
Mid(strName, InStr(strName, " "), 1) = "_"
Resume
Else
MsgBox Err.Description
Resume ExitHere
End If

Case Else
MsgBox Err.Description
Resume ExitHere
End Select

End Sub
Whoops, you can remove: Range("iu98").ClearContents I left it in inadvertantly, it's of no use. Nate : MsgBox ("Cell A2's contents are empty, cannot name a range as such") : End Sub

Posted by Nate Oliver on January 17, 2002 3:29 PM

Re: Are you one-upping me?! :)

Well, at the time I posted I was one-upping myself...In any case, I tried this on Excel 2000 and it only named the range on the active sheet. Not only that, but it cleared out named ranges on other sheets.

Cheers, Nate

Posted by Russell Hauf on January 18, 2002 1:55 PM

Re: Are you one-upping me?! :)

Just playing around. By the way, the code worked for me (I just tried it again).

rh Well, at the time I posted I was one-upping myself...In any case, I tried this on Excel 2000 and it only named the range on the active sheet. Not only that, but it cleared out named ranges on other sheets. Cheers, Nate