Changing the name of a sequence in Microsoft Excel

To rename each named range in the active workbook, you can cycle through the Names list. If your range names consistently include a “_” as a separator between the file name and range name, you can use a string manipulation transformation. These solutions may be helpful. In column A, if you have the headers “Assuming that” and the series data is in columns B:F, you could use Solution 1, which involves a formula that can be adjusted to fit your needs.


Solution:

You have the option to loop through the Names list in the current workbook to access each named range and modify its name, as illustrated in the 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

If your file names and range names are consistently separated by an underscore, you can utilize this transformation for manipulating strings.

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

Hope that helps.

Good luck – MikeD

Frequently Asked Questions