Excel Macro to name ranges according to the name of the worksheet Tab name

Question

I have the following code which I am trying to get to name the entire A and B columns range according to the worksheet tab name. I want each A:B range of cells in each worksheet to be named RoomCode_ + the name of the excel sheet tab.

So for example if I had 3 sheets called XYZ, ABC and DEF, then my cell range names for those 3 sheets respectively should be:

RoomCode_XYZ
RoomCode_ABC
RoomCode_DEF

I would typically do this manually by highlighting the cell range and just typing the range name I wanted, however I have over 150 tabs and would like to be able to do them all automatically through this process.

Sub nameRanges()

Set wbook = ActiveWorkbook

For Each sht In wbook.Worksheets
    sht.Activate

RangeName = "RoomCode_" + ActiveSheet.Name
  CellName = "A:B"

  Set cell = ActiveWorksheets.Range(CellName)
  ActiveWorksheets.Names.Add Name:=RangeName, RefersTo:=cell

  Next sht

End Sub

I believe naming rules for worksheets and ranges are different, and that you'll run into problems if a sheet/tab has spaces in its name, for example. — Mathieu Guindon, Jun 29 '16 at 16:30

score 1 · Accepted Answer · edited May 23 '17 at 11:58

Just a bit of refactoring to get what you need. Biggest this to work directly with objects and eliminate the Active... stuff.

Also ActiveWorksheets is not proper syntax in any way.

Sub nameRanges()

    Dim wbook As Workbook
    Set wbook = ThisWorkbook

    Dim sht As Worksheet

    For Each sht In wbook.Worksheets

        Dim RangeName As String, CellName As String

        RangeName = "RoomCode_" + sht.Name
        CellName = "A:B"

        Dim cell As Range

        Set cell = sht.Range(CellName)

        sht.Names.Add Name:=RangeName, RefersTo:=cell


    Next sht

End Sub

score 0 · Answer 2 · answered Jun 29 '16 at 16:31

Here's another way:

Option Explicit

Sub nameRanges()

Dim sht As Worksheet
Dim RangeName As String
Dim cell As String

For Each sht In ActiveWorkbook.Worksheets
    RangeName = "RoomCode_" + sht.Name
    cell = "=" & sht.Name & "!" & "A:B"
    Names.Add Name:=RangeName, RefersTo:=cell
Next sht

End Sub

score 0 · Answer 3 · answered Jun 29 '16 at 16:33

I think that you would want to add the names to the workbook names collection. The way it is now you'll still have to reference the individual worksheet before you can access the name.

WorkSheets("RoomCode").Range("RoomCode_XYZ")

By adding the names to the workbook you'll be able to access no matter the ActiveSheet.

Range("RoomCode_XYZ")

Sub nameRanges()

    Dim wbook As Workbook
    Set wbook = ThisWorkbook

    Dim sht As Worksheet

    For Each sht In wbook.Worksheets

        Dim RangeName As String, CellName As String

        RangeName = "RoomCode_" + sht.Name
        CellName = "A:B"

        Dim cell As Range

        Set cell = sht.Range(CellName)

        ThisWorkBook.Names.Add Name:=RangeName, RefersTo:=cell


    Next sht

End Sub

Excel Macro to name ranges according to the name of the worksheet Tab name

3 Answers3