如果从活动工作表调用命名范围，这里有一种方法可以找到它。最好先指定工作表ID。

Sub selectnamedRange()
Dim n As Name, someText As String, tRange As Range

'it would be better if this specified the sheet first such as sheet1.Range("A1")
someText = Range("A1").Value

For Each n In ThisWorkbook.Names
    'multiple conditions to account if in workbook or worksheet...
    If n.Name = someText Or InStr(1, "!" & n.Name, someText, vbTextCompare) Then
        'found the range
        Set tRange = n.RefersToRange
        Exit For
    End If
Next n

    tRange.Worksheet.Activate 'need this to jump sheets
    tRange.Select

End Sub

这是图纸AA

这是图纸BB

代码：
显式选项

Sub Go_Over_Names()
    'Store the Workbook
    Dim Wrk As Workbook: Set Wrk = ThisWorkbook
    'This vars AA and BB to store the sheets
    Dim BB As Worksheet: Set BB = Wrk.Worksheets("BB")
    Dim AA As Worksheet: Set AA = Wrk.Worksheets("AA")
    'Here store the range with the names in each cell in the Sheet BB
    Dim NmsRng As Range: Set NmsRng = BB.Range("A1:A4")
    'To use it in the for loop
    Dim i As Range
    Dim Rng As Range
    'to store the range name and the sheet where the range exists
    Dim NmStr As String
    Dim NmSht As String
    'To store the sheet where the name exists
    Dim Sht As Worksheet
    
    'Let's work... Go the Sheet (BB) where the names (Strings) exist
    BB.Activate
    'Loop over the cells with the 150 names... Just 3 but you know...
    For Each i In NmsRng
        'Store the name range into the var
        NmStr = i.Value
        'Sice the name ranges have the scope for the whole workbook, we can
        'take advantage of that.
        'if not, it won't be any trouble. 
        Set Rng = Range(NmStr)
        'Here we take the name of the sheet where the actual range exist...
        NmSht = Rng.Parent.Name
        Set Sht = Wrk.Worksheets(NmSht)
        'Let's go to that sheet
        Sht.Activate
        'Here you can do whatever you want with your range...
        'select...
        ' As @pgSystemTester say, the Select statement, is not good the most of the times
        'Using the Rng var, you can copy, change value, loop and anything else
       'over the name range.
        Rng.Select
        'Change the value.
        Rng.Value = 1
    Next i
End Sub

结果：

注意：Name Range EE_004的作用域仅限于工作表AA，因此，如果该范围是全局（工作簿）或仅在工作表上，则不会有任何问题。其他范围是全局。

使用列中的列表循环命名范围

• 假定名称是工作簿范围的。

Sub ProcessNamedRanges()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim sws As Worksheet: Set sws = wb.Sheets("Sheet2")
    Dim srg As Range: Set srg = sws.Range("A1:A150")
    
    Dim drg As Range, sCell As Range
    
    For Each sCell In srg.Cells
        On Error Resume Next
            Set drg = wb.Names(sCell.Value).RefersToRange
        On Error GoTo 0
        If Not drg Is Nothing Then
            ' Perform operations e.g.:
            Debug.Print drg.Worksheet.Name, drg.Address
            
            Set drg = Nothing ' reset for the next iteration
        Else
            ' invalid name or is not a range reference
            ' or is not of workbook scope...:
            Debug.Print "Skipped """ & Cstr(sCell.Value) & """."
        End If
    Next sCell
    
    MsgBox "Named ranges processed.", vbInformation

End Sub