Creating equivalent named ranges on a new sheet

[John]

I have a macro that creates a new sheet identical to an existing sheet using
Sheets("Robin").Select
Sheets("Robin").Copy Before:=Sheets(1)
Sheets("Robin (2)").Name = TeamMember
The original sheet has a couple named ranges on it and I want to create two
new named ranges for the new sheet that are exactly the same as those on the
old sheet, but have new names that do not conflict w/ the old names. How do I
do this?

Thx for your help. -John

 

[Rick Rothstein]

You didn't give us any specifics, but something along these lines should work...

Sheets("Robin").Copy Before:=Sheets(1)
Sheets(1).Name = TeamMember
With ThisWorkbook
.Names.Add Name:="YourFirstNewName", _
RefersTo:=TeamMember & "!$A$1:$H$7"
.Names.Add Name:="YourSecondNewName", _
RefersTo:=TeamMember & "!$M$5:$Z$9"
End With

There are other parameters to the Add Method for the Names object, so you might want to check the help files for it.

 

[Gary Keramidas]

here's one approach I've used. just make sure the immediate window is visible
(control-G) and run this code. it will generate the necessary code to add the
ranges in another workbook, or just change the names. hopefully it won't wrap
when I post it.


Sub name_ranges2()
Dim nm As Name
sName = ActiveSheet.Name
For Each nm In ThisWorkbook.Names
Debug.Print "ActiveWorkbook.Names.Add Name:=" & """" & nm.Name & """" & _
", Refersto:=""" & "=" & sName & "!" & Range(nm).Address & """"
Next nm
End Sub