Copy and paste Named Ranges

[Robert H]

I copy a range of cells from a template sheet and paste them during
the creation of other sheets. The range is defined by a named range.

Public Sub InsStatRows()
'Insert Statistics Footer
Dim mySheet
Dim mySpace As Range

Set mySheet = ActiveSheet

Range("a1").End(xlDown).Offset(2, 0).Select

Set mySpace = Selection

Worksheets("Template").Range("statRows").Names

ActiveSheet.Paste Destination:=ActiveSheet.Range(mySpace.Address)

End Sub

This works great but I also need to copy Named Ranges that are within
the source range. (some of the copied cells have names in them) Is it
possible to copy named ranges from one sheet to another? If yes,
suggestions please
These names are "local"
Robert

 

[Robert H]

To clarify, I mean to copy the actual names over to the new sheet.

 

[Robert H]

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.....

 

[Tom Ogilvy]

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

 

[Robert H]

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

 

[Tom Ogilvy]

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.

 

[Robert H]

I was trying to work at the sheet level originaly but could not get
that to work. I was probably not referencing the sheets collection
correctly. Looking back at your code again, its obvious there is a
names collection at the sheet level as you used
"Sheets("Source").Names"

I think Ill go out side and rub may face on the sidewalk

Thanks for explaining
Robert