Named Range Collection

[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

 

[Chip Pearson]

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]

Excellent.

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)

 

[DocBrown]

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]

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)