如何根据Excel单元格文本动态引用不同工作表（VBA实现）

步骤1：设置可选择工作表名的下拉列表 #

先指定一个单元格（比如A1）作为工作表选择器，给它添加数据验证下拉列表：

• 选中目标单元格（如A1），点击「数据」选项卡 → 「数据验证」
• 在弹出窗口中，允许类型选「序列」，来源可直接输入需要的工作表名（用逗号分隔），或用动态公式自动获取当前工作簿所有工作表名：

  公式：=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
  注意：该公式需启用宏，首次使用可能需要允许函数运行

步骤2：修改VBA中的Lookup子过程 #

把原代码中固定的"MASTER"替换为从指定单元格读取的变量，核心修改如下：

原代码示例（固定工作表） #

Sub LookupData()
    Dim result As Variant
    ' 固定引用MASTER表
    result = Application.XLookup(Range("B2"), Sheets("MASTER").Range("A:A"), Sheets("MASTER").Range("B:B"), "未找到")
    Range("C2").Value = result
End Sub

修改后的动态代码 #

Sub DynamicLookupData()
    Dim wsName As String
    Dim targetWs As Worksheet
    Dim result As Variant
    
    ' 从指定单元格（比如A1）获取工作表名
    wsName = Trim(Range("A1").Value)
    
    ' 错误处理：检查单元格是否为空或工作表不存在
    If wsName = "" Then
        MsgBox "请先选择工作表", vbExclamation
        Exit Sub
    End If
    
    On Error Resume Next
    Set targetWs = ThisWorkbook.Sheets(wsName)
    On Error GoTo 0
    
    If targetWs Is Nothing Then
        MsgBox "工作表[" & wsName & "]不存在", vbCritical
        Exit Sub
    End If
    
    ' 用动态获取的工作表执行Xlookup
    result = Application.XLookup(Range("B2"), targetWs.Range("A:A"), targetWs.Range("B:B"), "未找到")
    Range("C2").Value = result
End Sub

额外优化建议 #

如果需要多个Lookup子过程复用这个逻辑，可以把获取工作表的代码抽成单独函数：

Function GetTargetWorksheet(wsNameCell As Range) As Worksheet
    Dim wsName As String
    wsName = Trim(wsNameCell.Value)
    
    If wsName = "" Then
        MsgBox "请选择工作表", vbExclamation
        Set GetTargetWorksheet = Nothing
        Exit Function
    End If
    
    On Error Resume Next
    Set GetTargetWorksheet = ThisWorkbook.Sheets(wsName)
    On Error GoTo 0
    
    If GetTargetWorksheet Is Nothing Then
        MsgBox "工作表[" & wsName & "]不存在", vbCritical
    End If
End Function

调用示例：

Sub AnotherLookup()
    Dim targetWs As Worksheet
    Set targetWs = GetTargetWorksheet(Range("A1"))
    If targetWs Is Nothing Then Exit Sub
    
    ' 执行其他Xlookup逻辑
    ' ...
End Sub

内容的提问来源于stack exchange，提问作者Ahoycaptain10234