Creating Local Range Names in Multiple Sheets

By Jeff Robson

Working with grouped sheets is a great way of manipulating lots of sheets at once and eliminating repetitive tasks.

Excel also lets you have the same range name on multiple sheets, each with its scope set to that sheet only.

But what if you want to use a new range name on your grouped sheets? Normally you’d have to go and create this manually on each of the sheets in your target group of sheets … a very time-consuming process if you have a lot of sheets in the group.

Here’s a little macro that will help to make your life easier!

In our file, we assigned it to the keyboard shortcut Ctrl-Shift-R

In our file, all grouped sheets have the same tab colour, the index number of which is stored in a range name called refDataSheetColour.

The formula in this range name is simply =SheetColour(Template!A1) since Template is the sheet name of one of our grouped sheets.
The code for this user-defined function is:

Function SheetColour(Optional rngRange As Range) As String
‘************************************************************
‘**
‘** PROCEDURE NAME:
‘**      SheetColour
‘**
‘** FUNCTION DESCRIPTION :
‘**      Returns the sheet index colour of the cell/range passed to it
‘**
‘** AUTHOR :
‘**      Jeff Robson
‘**
‘** CREATION DATE :
‘**      Jul 2010
‘**
‘** INPUT PARAMETER LIST :
‘**      rngRange  relevant range
‘**
‘** RETURN VALUE :
‘**      sheet name as string
‘**
‘** REVISIONS :
‘**
‘************************************************************
    Application.Volatile
    If rngRange Is Nothing Then
        Set rngRange = Application.Caller
    End If
    SheetColour = rngRange.Parent.Tab.ColorIndex
End Function
(Note: Feel free to change the spelling of “colour” if you’re in North America!)
We then use the following code to create the local range names in all the sheets that have the right tab colour:
Sub CreateRangeName()
    Dim rngTarget As Range
    Dim wksThis As Worksheet
    Dim stRangeName, stOutput As String
    Dim i As Integer
    ‘assigned to Ctrl+Shift+R
    ‘get the user to select the range of cells to be named and the range name to be created
    Set rngTarget = Application.InputBox(prompt:=”Select target range”, Title:=”Select Range”, Type:=8)
    stRangeName = InputBox(prompt:=”Enter Range Name”, Title:=”Range Name”)
    ‘exit if inputbox cancelled
    If rngTarget Is Nothing Then Exit Sub
    
    If NameExists(stRangeName) = False Then
        MsgBox prompt:=”The name ‘” & stRangeName & “‘ cannot be created because it already exists in the file.”, Buttons:=vbOKOnly + vbCritical, Title:=”Name Error”
        Exit Sub
    End If

  

    ‘create the range name on each worksheet that has the right tab color
    For Each wksThis In Worksheets
        If wksThis.Tab.ColorIndex =

[refDataSheetColour] Then

            wksThis.Names.Add Name:=stRangeName, RefersTo:=”='” & wksThis.Name & “‘!” & rngTarget.Address
            i = i + 1
        End If
    Next

   

    ‘create the output message
    stOutput = i & ” name”
    If i <> 1 Then
        stOutput = stOutput & “s”
    End If
    stOutput = stOutput & ” created”
    MsgBox stOutput, vbOKOnly + vbInformation, Title:=”Name Creation Complete”
End Sub
This code also makes use of another handy user-defined function we’ve written that simply checks whether a particular range name exists in the workbook:
Public Function NameExists(stName) As Boolean
    ‘returns true if the name exists in the current workbook
    Dim nmName As Name
    Dim i As Integer
    NameExists = False
    For Each nmName In Names
        If nmName.Name = stName Or nmName.Name = “‘” & ActiveSheet.Name & “‘!” & stName Or nmName.Name = ActiveSheet.Name & “!” & stName Then
            NameExists = True
        End If
    Next
End Function