Named Range Collection

  • Thread starter Thread starter ExcelMonkey
  • Start date Start date
E

ExcelMonkey

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

Thanks

EM

Chip Pearson said:
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)
Click to expand...
 
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
 
ThisWorkbook.Worksheet("Lists).Names =
workbooks("OtherWB").Worksheets("Lists").Names
Click to expand...

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)
 
Back
Top