Thanks Tom your code look a little cleaner than what I eventually came
up with. I'm actually glad no one replied for a few days because It
forced me to develop a much better understanding of names. Which seem
like they are on a sheet but are really off in space (names
Collection) and are just referred to a worksheet. True or not, that
explanation works for me at the moment :O.

Public Sub DuplicateNames()
'copy all names from the "template" sheet to all new sheets _
making them local to the new sheets.

Dim srcSht As Worksheet 'this should realy be a constant called
"Template"
Dim tgtSht As Worksheet
Dim nm As Name

'ID the Source and Target Sheets
Set srcSht = Worksheets("Source")
Set tgtSht = Worksheets("target")
'the target will be identified by a module or higher level
variable _
set by the calling procedure

'for each name that meets the sourceID criteria duplicate that name
replacing _
the source ID with the target ID

For Each nm In ActiveWorkbook.Names

'Criteria test (does the name refer to the correct sheet)
If InStr(1, nm.NameLocal, srcSht.Name) = 1 Then

'Add Name with new properties
Names.Add Replace(nm.Name, srcSht.Name & "!", tgtSht.Name
& "!"), _
Replace(nm.RefersTo, srcSht.Name & "!", tgtSht.Name & "!")

End If
Next
End Sub



On Feb 4, 2:28 pm, "Tom Ogilvy" wrote:
For sheet level names with sheets in the same workbook (Source and Target):

Public Sub CopyName()
Dim srcNme As Name
For Each srcNme In Sheets("Source").Names
Set rng = Sheets("target").Range( _
srcNme.RefersToRange.Address)
rng.Name = Replace(srcNme.Name, "Source", "Target")
Next srcNme
End Sub

--
Regards,
Tom Ogilvy

"Robert H" wrote in message

oups.com...

Experimenting I have set up a workbook with two sheets "Source" with
some local named ranges and "Target" (blank) to try and copy ranges
to. So far I have:

Public Sub CopyName()
Dim srcNme As Name

For Each srcNme In Sheets("Source").Names

Range(srcNme).Copy Destination:= _
Sheets("target").Range(Range(srcNme).Address)
'FIX - only pastes values

Next srcNme
End Sub

I have played around with a few different options but I can still only
get the values to show up on the target sheet. Im getting close to
the pulling my hair out point.....