Add Suffix To All Named Ranges

[Demo8II]

Hi, I've got two worksheets from different files i need to merge into one workbook....the problem being they both share a lot of the same named ranges (because I'm not very clever with my names).

I'm wondering if there is a way anybody knows of to add a suffix to the end of all the named ranges in one of the workbooks.


So basically what I am asking is this: if you have these two named ranges in a workbook - "MyRange1" and "MyRange2" - is there a way to quickly add a suffix to the end of them so that they now read "MyRange1Suffix" and "MyRange2Suffix"?

I know I can go through and do this manually, but I'm hoping there is a much better and faster way to do this with a macro.

Any help from the geniuses on this site would be very appreciated. Thanks in Advance.

Demo8II

 

[Jazzed]

If you don't mind adding a prefix such as "_" instead, maybe...

Sub AddPrefix()

Dim NameCount As Long
Dim i As Long

NameCount = ActiveWorkbook.Names.Count

For i = 1 To NameCount
    ActiveWorkbook.Names.Add Name:="_" & Names(i).Name, RefersTo:=Names(i)
    ActiveWorkbook.Names.Item(Names(i + 1).Name).Delete
Next
    
End Sub

 

[Demo8II]

Brilliant that did it....thanks for understanding the heart of my question.

Not that it matters now, but I wonder why that is the only way this can work...I tried adding in a word between the parenthesis but it didn't do anything.



Thanks so much!

 

[Jazzed]

Not that it matters now, but I wonder why that is the only way this can work...I tried adding in a word between the parenthesis but it didn't do anything.

Prepending the prefix "_" ensures that each new name appears before the old ones in the 'Names' collection. This is needed so that the old names can be deleted using the method outlined.

Thanks so much!

You are welcome!