Programatically add prefix to all named ranges

[Damo71]

In my workbook I have many named ranges. I want to add a prefix "P_" to each name.

Is there a way I can do this with VBA to save time?

Thanks

 

[mikerickson]

You could use this sub

Sub Test()
    Dim oneName As Name
    For Each oneName In ThisWorkbook.Names
        If TypeName(Evaluate(oneName.RefersTo)) = "Range" Then
            If Not (oneName.Name Like "P_*") Then
                oneName.RefersToRange.Name = "P_" & oneName.Name
                oneName.Delete
                Call Test
                Exit Sub
            End If
        End If
    Next oneName
End Sub

 

[Nine Zero]

Hey try this out

Sub AddPre()
Dim dataCol As Range
Dim r As Range
Set dataCol = Range("A1:A15")
For Each r In dataCol
r.Value = "P_" & r.Value
Next r
End Sub

just change the range to where ever your data is

 

[Damo71]

You could use this sub

oneName.RefersToRange.Name = "P_" & oneName.Name

Thanks a lot for your help.

I'm getting the error "Wrong number of arguments or invalid property assignment" error message when I execute the code, it's showing the error on the above line.

 

[Damo71]

Hey try this out

Sub AddPre()
Dim dataCol As Range
Dim r As Range
Set dataCol = Range("A1:A15")
For Each r In dataCol
r.Value = "P_" & r.Value
Next r
End Sub

just change the range to where ever your data is

Hi, thanks for your help too.

Sorry, perhaps my explanation was bad. I mean I need to change the Named Ranges themselves, not the information in the cells to which they refer.

So let's say I have named A1:A15 "MyFirstRange", I need to change the name of the range to "P_MyFirstRange"

Thanks again

 

[mikerickson]

Thanks a lot for your help.

I'm getting the error "Wrong number of arguments or invalid property assignment" error message when I execute the code, it's showing the error on the above line.

Hmm.. it works for me. What is the value of oneName.Name and OneName.RefersToRange.Address when the error occurs?

 

[Damo71]

Hmm.. it works for me. What is the value of oneName.Name and OneName.RefersToRange.Address when the error occurs?

Hi Mike

How would I go about finding that out?

P.S. Most of the names are dynamic, would that make a difference?

 

[mikerickson]

What would the RefersTo of a named range be, typically.

 

[Damo71]

What would the RefersTo of a named range be, typically.

Oh, I see, sorry. So one named range is "BroIsOnHoliday" and the formula in the RefersTo box is:

=OFFSET(BroList!$AK$1,1,0,COUNTA(BroList!$A:$A)-1,1)

Most have similar structures, but some are straight forward references like

(RefersTo:) =Home!$A$1:$A$2

 

[mikerickson]

Try this

Sub Test()
    Dim oneName As Name
    For Each oneName In ThisWorkbook.Names
        If Not (oneName.Name Like "P_*") Then
            ThisWorkbook.Names.Add Name:= "P_" & oneName.Name, RefersTo:=oneName.RefersTo
            oneName.Delete
        End If
    Next oneName
End Sub