Range NAme Macro

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

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

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

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

rh

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

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

FANTASTIC! - Thanks guys

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

FANTASTIC! - Thanks guys

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

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

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

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 

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

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