Applying Name Change to Existing Formulas

[pulchramo574]

Hi,

I've got a vast amount of names in my workbook (>100) for one time period, and have decided I want to expand my workbook to include data from multiple time periods. Hence I want to add a "_HY17" string to the end of every single one of my names, and have them updated in the existing formulas. Would anyone know of a quick way to do this using VBA?

Cheers.

 

[DushiPunda]

So I just did a test.

1. I named Cell A1 "test"
2. I typed 123 into Cell A1
3. Formula in Cell B1: =test, value displayed: 123
4. I created this macro and then ran it:

Sub testnames()
Dim nm As Name
For Each nm In ActiveWorkbook.Names
    nm.Name = nm.Name & "_456"
Next
End Sub

5. Cell A1 is now named "test_456"
6. The formula in Cell B1 is now: =test_456, value displayed: 123

All of that to illustrate that the simple macro I have above should do what you want it to do. You just need to change:

This:
nm.Name = nm.Name & "_456"

To:
nm.Name = nm.Name & "_HY17"

 

[pulchramo574]

Worked a treat, thanks a bunch mate