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

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