Apparently you still don't have it completely clear <g Hope this helps.

Each sheet has its own names collection. If the name is like

Sheet2!MyName

then the name is in the Names collection for Sheet2 (tab name sheet2)

If the name is like

MyName
then it is a workbook level name. However, the names collection for the
workbook includes all names including sheet level names.

Since you were only working with sheetlevel names for the source sheet, my
code was written appropriately.

--
regards,
Tom Ogilvy




"Robert H" wrote in message
oups.com...
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.....