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.

 

[Trevor G]

Welcome to the forum,

Give this ago

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

 

[dsorensen]

Welcome to the forum,

Give this ago

Trevor,
Thanks for the quick response but this didn't work none of my named ranges changed. Any other ideas?
Thanks

 

[Gavin T]

I think Trevor G meant to write this:

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

 

[dsorensen]

Gavin,
I made the change to the For next statement when I tried this originally and it didn't work.
Thanks

 

[Gavin T]

It's case sensitive so try to:

Replace:

Nam.Name = WorksheetFunction.Substitute(Nam.Name, "Prop", "CovA")

With:

Nam.Name = WorksheetFunction.Substitute(Nam.Name, "prop", "CovA")

 

[dsorensen]

Nope still didn't work. The names are Prop___ so "Prop" should have worked if this code was going to work.
Any other ideas?

 

[dsorensen]

The issue is that the names begin with Prop but then their is always something after it. I made a range and named it Prop and the macro worked and changed it to CovA. Is there a way to say if it starts with Prop change it from Prop to CovA reguardless of what comes after Prop?

 

[Gavin T]

The code above does that, I don't know why it's not working for you

 

[dsorensen]

Does it need something after Prop to say that there is another part of the name?