Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste Named Ranges
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste Named Ranges
To clarify, I mean to copy the actual names over to the new sheet.
On Feb 1, 3:51 pm, "Robert H" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste Named Ranges
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..... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste Named Ranges
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..... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste Named Ranges
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..... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste Named Ranges
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..... |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste Named Ranges
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
trying to copy a worksheet containing named ranges to anotherworksheet | Excel Worksheet Functions | |||
Copy worksheet with named ranges to new workbook and keep names | Excel Worksheet Functions | |||
Copy data in named ranges to a newer version of the same template to identical ranges | Excel Programming | |||
copy the valued of named ranges | Excel Programming | |||
copy all named ranges in a sheet to seperate sheets | Excel Programming |