MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copying Named Ranges/Cells

Posted by Jill-ann on April 25, 2000 4:56 PM

I have a worksheet that includes about 50 named ranges. The first three letters of each range indicates the City for which the worksheet is used. I want to copy that worksheet and just change the name. However, the only way I've found to change the name of these ranges is to add a new name and delete the old one. There has got to be a faster way! (I'll be using this for about 15 cities. Can anyone teach me a quicker method??????? Thanks!

Posted by Ivan Moala on April 26, 2000 6:16 PM

Hi Jill-Ann
There is a way but via macros
Have a look @ the following;
Just change the City variable to your city Text.
If you require further then just post.


Sub ChangeNames()
Dim OldNm
Dim Nms
Dim City As String
Dim Temp() As String
Dim NmTemp() As String
Dim x As Integer
Dim y As Integer

City = "CAL"

Set Nms = ActiveWorkbook.Names
y = Nms.Count
ReDim Temp(y)
ReDim NmTemp(y)

For x = 1 To y
NmTemp(x) = City & Right(Nms(x).Name, Len(Nms(x).Name) - 3)
Temp(x) = Nms(x).RefersToR1C1

For Each OldNm In Nms

For x = 1 To y
ActiveWorkbook.Names.Add Name:=NmTemp(x), RefersToR1C1:=Temp(x)
End Sub

Posted by Jill-ann on April 27, 2000 1:06 PM


I can't tell you how grateful I am for your response. It is EXACTLY what I need. Can you spare another moment or two and help me once more? I don't know what I'm doing wrong, but here's what I did:

I copied the SEA worksheet using the Move or Copy Sheet option. In doing that the sales matrix is called SEASales. I need to change it to SFOSales. But I need the sales matrix in the SEA worksheet within the same workbook to remain SEASales. I copied the code that you provided "behind the sheet" for San Francisco and ran the macro. It removed all the named ranges in all the worksheets except 2. Also, I received an error "Run-Time Error '1004': Application-defined or object-defined error" When I hit "debug" it takes me to the 3rd line from the bottom "ActiveWorkbook.Names.Add Name:=NmTemp(x), RefersToR1C1:=Temp(x)"

What did I do wrong? I would appreciate any help you can offer!

Thanks Again!

Posted by Ivan Moala on April 27, 2000 3:11 PM


Hi Jill-Ann
Without going to great lenths it may be easier
if I sought this off line.