Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range Collection
Is there a named range collection in Excel? Or would I have to create this
collection by looping through all the cells in the UsedRanage and testing to see if each range had a name? Thanks EM |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range Collection
The Workbook.Names collection contains all the workbook-wide names. The
Worksheet.Names collection contains the sheet level names. E.g., Dim WS As Worksheet Dim N As Name For Each N In ThisWorkbook.Names Debug.Print N.Name, N.RefersTo Next N For Each WS In ThisWorkbook.Worksheets For Each N In WS.Names Debug.Print N.Name, N.RefersTo Next N Next WS -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "ExcelMonkey" wrote in message ... Is there a named range collection in Excel? Or would I have to create this collection by looping through all the cells in the UsedRanage and testing to see if each range had a name? Thanks EM |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range Collection
Excellent.
Thanks EM "Chip Pearson" wrote: The Workbook.Names collection contains all the workbook-wide names. The Worksheet.Names collection contains the sheet level names. E.g., Dim WS As Worksheet Dim N As Name For Each N In ThisWorkbook.Names Debug.Print N.Name, N.RefersTo Next N For Each WS In ThisWorkbook.Worksheets For Each N In WS.Names Debug.Print N.Name, N.RefersTo Next N Next WS -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "ExcelMonkey" wrote in message ... Is there a named range collection in Excel? Or would I have to create this collection by looping through all the cells in the UsedRanage and testing to see if each range had a name? Thanks EM |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range Collection
So, If I had a WS with many named ranges and I wanted to copy them to a
different WS in a different WB, can I copy the whole collection in one assignement: ThisWorkbook.Worksheet("Lists).Names = workbooks("OtherWB").Worksheets("Lists").Names Or do I need to loop through the list? Thanks, John "Chip Pearson" wrote: The Workbook.Names collection contains all the workbook-wide names. The Worksheet.Names collection contains the sheet level names. E.g., Dim WS As Worksheet Dim N As Name For Each N In ThisWorkbook.Names Debug.Print N.Name, N.RefersTo Next N For Each WS In ThisWorkbook.Worksheets For Each N In WS.Names Debug.Print N.Name, N.RefersTo Next N Next WS -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "ExcelMonkey" wrote in message ... Is there a named range collection in Excel? Or would I have to create this collection by looping through all the cells in the UsedRanage and testing to see if each range had a name? Thanks EM |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range Collection
ThisWorkbook.Worksheet("Lists).Names = workbooks("OtherWB").Worksheets("Lists").Names That won't work at all. It won't even compile. To copy the Names from Book1 to Book2, such that the new names in Book2 refer to the same cell addresses, use Dim WB1 As Workbook Dim WB2 As Workbook Dim NM As Name Set WB1 = Workbooks("Book1") Set WB2 = Workbooks("Book2") For Each NM In WB1.Names WB2.Names.Add Name:=NM.Name, RefersTo:=NM.RefersTo Next NM If you want to copy the data along with the names, use Dim WB1 As Workbook Dim WB2 As Workbook Dim NM As Name Set WB1 = Workbooks("Book1") Set WB2 = Workbooks("Book2") For Each NM In WB1.Names WB2.Names.Add Name:=NM.Name, RefersTo:=NM.RefersTo WB2.Names(NM.Name).RefersToRange.Value = NM.RefersToRange.Value Next NM Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Mon, 24 Aug 2009 08:11:01 -0700, DocBrown wrote: So, If I had a WS with many named ranges and I wanted to copy them to a different WS in a different WB, can I copy the whole collection in one assignement: ThisWorkbook.Worksheet("Lists).Names = workbooks("OtherWB").Worksheets("Lists").Names Or do I need to loop through the list? Thanks, John "Chip Pearson" wrote: The Workbook.Names collection contains all the workbook-wide names. The Worksheet.Names collection contains the sheet level names. E.g., Dim WS As Worksheet Dim N As Name For Each N In ThisWorkbook.Names Debug.Print N.Name, N.RefersTo Next N For Each WS In ThisWorkbook.Worksheets For Each N In WS.Names Debug.Print N.Name, N.RefersTo Next N Next WS -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "ExcelMonkey" wrote in message ... Is there a named range collection in Excel? Or would I have to create this collection by looping through all the cells in the UsedRanage and testing to see if each range had a name? Thanks EM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting a collection of named ranges using wildcard characters? | Excel Programming | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
If any cell in named range = 8 then shade named range | Excel Programming | |||
Range collection | Excel Programming | |||
Deleting named ranges by looping through range collection | Excel Programming |