Change Range Names with VBA Macro.

[dsorensen]

I am trying to change some range names on different sheets so I can do this faster than doing it manually. I have about 30 ranges for each sheet and they are all named Prop___ i.e. Prop1, Prop2, PropLAE1, PropSUB1... and I want to change them to CovA___.
I need to do this for 5 different sheets so if there is a VBA script to do this with out having to name all of the ranges that would be great. I have excel 2013 professional if that changes anything.

Thanks in advance.

 

[Gavin T]

Nope, the worksheet function "substitute" does it for you. See Excel SUBSTITUTE Function

 

[dsorensen]

I closed everything down and it worked. Now i get to figure out how to do this for a single sheet instead of the whole workbook.
Thanks for all of your help.

 

[Laurie Brainerd]

Thank you for the code above as it worked for me, too. But I haven't figured out how to modify it so that it only worked on the active worksheet?

Sub ChangeName()
Dim Nam As Name
For Each Nam In ThisWorkbook.Names
Nam.Name = WorksheetFunction.Substitute(Nam.Name, "22", "21")
Next Nam
End Sub