Extract list of all named ranges in a worksheet

[mikeymay]

I am having a problem with extracting all the named ranges in a worksheet.

I thought the following would work but the there are no named ranges detected by the code

Sub CopyToV7()

Dim wbV6 As Workbook

Dim wsV6 As Worksheet

Dim rngV6 As Range

Dim nmV6 As Name

Set wbV6 = ActiveWorkbook

Set wsV6 = wbV6.Sheets("File Data")

For Each nmV6 In wsV6.Names
    Debug.Print nmV6.Name, Range(nmV6).Address
Next

Set wsV6 = Nothing

Set wbV6 = Nothing

End Sub

From the above Watches window, the Names expression states there are none.

But in the Name Manager in Excel, there are a lot of named ranges

I have used this code before to extract the named ranges so I am not sure what, if anything has changed for this to now not work.

TIA[/CODE]

 

[saboh12617]

Hello,

That is because those "named ranges" are defined with scope Workbook. Therefore you can only access them through Workbook.Names property (Excel) | Microsoft Learn.

The Worksheet.Names property (Excel) | Microsoft Learn is used for named ranges with scope worksheet.