I found a lot of examples but it's not working in my case and I don't know why. Very basic code:
Sub Test()
Dim namCur As Name
For Each namCur In ActiveSheet.Names
MsgBox "Name: " & namCur.Name & ", Refers To: " & namCur.RefersTo
Next namCur
End Sub
And I have the same issue when I use Worksheets("Assumptions").Names
When I watch ActiveSheet.Name
, this is correct I get "Assumptions", you can see on the picture below the list of named ranges. But I never get the MsgBox and the For loop goes directly to the end. Edit: Very important, I need to loop only this sheet's named ranges, not the whole workbook Any idea?
I use Excel 2016
Your solution will only list Names that have a scope set to only the ActiveSheet.
Change this
For Each namCur In ActiveSheet.Names
To this
For Each namCur In ThisWorkBook.Names
to list all names in the Workbook. You can then check the RefersTo address to check if it applies to the ActiveSheet.
Sub Test()
Dim namCur As Name
Dim TargetSheetName As String
TargetSheetName = "Assumptions"
For Each namCur In ThisWorkbook.Names
If Range(namCur.RefersTo).Parent.Name = TargetSheetName Then MsgBox "Name: " & namCur.Name & ", Refers To: " & namCur.RefersTo
Next namCur
End Sub
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments