Copying Named Ranges/Cells

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.

Ivan

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
Next

For Each OldNm In Nms
OldNm.Delete
Next

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

Ivan,

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!

Ivan,

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

Ivan