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

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

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

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

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