How to Rename a series or range names and make their scope global

Question

• I have 2 template sheets (in Excell 2010) called “DefaultDesign” and “DefaultDesignMaterials” with a number of cell names with a workbook scope. For Example, a range name is called “DefaultDesign_NumberOfServers” with workgroup scope. I want to copy these 2 template sheets and rename them to “Design1” and “Design1Materials”, and then rename the cell name (or range) “DefaultDesign_NumberOfServers” to “Design1_NumberOfServers” and "DefaultDesign_OS" to "Design1_OS", etc., etc... All with a workgroup scope. There are too many cell names and cell-range names to rename manually.

Answer 1

You can cycle through the Names list of the active workbook to get hold of each named range, and rename it - example

Sub RangeRename()
Dim N As Name

    For Each N In ActiveWorkbook.Names
        N.Name = N.Name & "_New" ' or any other transformation
    Next N
End Sub

For the string manipulation - if your names consistently contain a "_" as seperator between file name and range name, you can use this transformation:

N.Name = ActiveWorkbook.Name & "_" & Mid(N.Name, InStr(1, N.Name, "_") + 1, 99)

Hope that helps.

Good luck - MikeD