如何用VBA动态识别Excel工作表并批量执行Vlookup匹配？

核心问题修正 #

原代码的主要问题是硬编码了目标表名（'FOB 1'），且循环逻辑未按同前缀分组处理。下面是优化后的代码，实现按FOB/PPD前缀分组，动态匹配对应序号的前置工作表：

Sub DynamicVLookup()
    Dim wb As Workbook
    Dim fobSheets As Collection, ppdSheets As Collection
    Dim ws As Worksheet
    Dim i As Integer, j As Integer
    Dim lastRow As Long
    Dim targetCol As Integer
    
    Set wb = ActiveWorkbook
    Set fobSheets = New Collection
    Set ppdSheets = New Collection
    
    ' 1. 按前缀分组收集工作表，并按序号排序
    On Error Resume Next ' 处理重复序号（实际场景建议提前排查）
    For Each ws In wb.Sheets
        If ws.Name Like "FOB *" Then
            ' 提取序号，按序号加入集合（自动排序）
            fobSheets.Add ws, Key:=CStr(Val(Mid(ws.Name, 5)))
        ElseIf ws.Name Like "PPD *" Then
            ppdSheets.Add ws, Key:=CStr(Val(Mid(ws.Name, 5)))
        End If
    Next ws
    On Error GoTo 0
    
    ' 2. 处理FOB组工作表
    For i = 2 To fobSheets.Count
        Set ws = fobSheets(i)
        lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
        targetCol = 3 ' 从C列开始插入匹配列
        
        ' 匹配所有前置FOB工作表
        For j = 1 To i - 1
            ws.Columns(targetCol).Insert Shift:=xlToRight
            ' 动态生成Vlookup公式，使用前置工作表名称
            ws.Cells(2, targetCol).Resize(lastRow - 1).FormulaR1C1 = _
                "=VLOOKUP(RC[-1],'" & fobSheets(j).Name & "'!C:C,1,0)"
            targetCol = targetCol + 1
        Next j
    Next i
    
    ' 3. 处理PPD组工作表（逻辑同FOB）
    For i = 2 To ppdSheets.Count
        Set ws = ppdSheets(i)
        lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
        targetCol = 3
        
        For j = 1 To i - 1
            ws.Columns(targetCol).Insert Shift:=xlToRight
            ws.Cells(2, targetCol).Resize(lastRow - 1).FormulaR1C1 = _
                "=VLOOKUP(RC[-1],'" & ppdSheets(j).Name & "'!C:C,1,0)"
            targetCol = targetCol + 1
        Next j
    Next i
    
    ' 激活第一个工作表
    wb.Sheets(1).Activate
End Sub

关键优化点 #

• 分组收集：用Collection按前缀分组，通过序号作为Key实现自动排序，确保工作表按FOB 1→FOB 2→FOB 3的顺序处理
• 动态表名：直接从集合中获取前置工作表的名称，替换硬编码的'FOB 1'，实现完全动态匹配
• 多前置匹配：支持后续工作表匹配所有同前缀的前置表（比如FOB3同时匹配FOB1和FOB2）
• 避免激活工作表：原代码的.Activate会降低效率，优化后直接通过对象引用操作工作表

注意事项 #

• 确保工作表名称格式严格为FOB X或PPD X（X为阿拉伯数字），否则序号提取逻辑需要调整
• 如果存在重复序号的工作表，代码会自动跳过（On Error Resume Next的作用），建议提前清理重复命名的表

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