Looping through all named ranges in excel VBA in current active sheet

Question

Solution 1

If you only want to get names from the active worksheet, use this:

Sub Test()
For Each nm In ActiveWorkbook.Names
    If nm.RefersToRange.Parent.Name = ActiveSheet.Name Then MsgBox nm.Name
Next nm
End Sub

^ This code will only return named ranges that refer to ranges on the active worksheet.

nm.RefersToRange.Parent will return the worksheet associated with the range.

We can then retrieve its name using .Name and compare it to the ActiveWorksheet name.

Here you can see I have 2 Named Ranges on Sheet4 and 1 on Sheet3

When I run this code, it only returns MyName1 and MyName2 - it does not include MyName3 as it is not on the active sheet.

This macro will only return the ones on my ActiveSheet (Sheet4)

Solution 2

This macro will loop through all named ranges in your workbook. It takes the comments above into consideration and shows how you can do things with specific ranges.

Sub nameLoop()
Dim nm
Dim wb As Workbook
Set wb = ActiveWorkbook
For Each nm In wb.Names
    If InStr(1, nm.Name, "data") Then
        ' Do stuff with the named range.
        Debug.Print nm.Name
    End If
Next nm
End Sub

Note, using InStr() will find "data" anywhere in the name. So if you have data1, datas1, and myData1, it'll run the "do stuff here" code for all of those. If you just want ranges starting with data, then change the InStr() line to: If Left(nm.Name,4) = "data" Then

Edit: You can couple this with the If nm.RefersToRange.Parent.Name = ActiveSheet.Name line suggested by @user1274820 below. (Just make the If statement include an And operator).