VBA遍历文件时无指定Worksheet需跳过对应执行代码的问题

问题根因 #

运行时错误9（下标越界）由直接访问不存在的Managers工作表触发，你之前用的On Error GoTo逻辑没有在单次循环结束后清除错误状态，多文件循环时错误累积就会触发崩溃，替换为主动判断工作表是否存在的逻辑即可解决。

修复方案 #

步骤1：新增工作表存在性判断函数 #

把下面这段代码放在你的VBA模块所有过程的最前面：

' 自定义函数：判断指定工作簿中是否存在对应名称的工作表
Function SheetExists(shtName As String, wb As Workbook) As Boolean
    Dim sht As Worksheet
    On Error Resume Next
    Set sht = wb.Sheets(shtName)
    On Error GoTo 0
    SheetExists = Not sht Is Nothing
End Function

步骤2：替换原有Merge过程代码 #

下面的代码保留了你原有功能逻辑，新增了Managers工作表判断，同时优化了没必要的Activate/Select操作，运行效率更高：

Sub Merge()
    Dim TargetFiles As FileDialog
    Dim FileIdx As Long
    Dim Databook As Workbook
    Dim MasterWB As Workbook
    
    ' 提前绑定汇总工作簿，避免重复调用
    Set MasterWB = Workbooks("Master Review - Test File")
    
    ' 优化运行速度配置
    Application.DisplayAlerts = False
    Application.Calculation = xlManual
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.AskToUpdateLinks = False
    Application.EnableEvents = False
    
    ' 唤起文件选择窗口
    Set TargetFiles = Application.FileDialog(msoFileDialogOpen)
    With TargetFiles
        .AllowMultiSelect = True
        .Title = "Select all files."
        .ButtonName = "确认"
        .Filters.Clear
        .Filters.Add ".xls* files", "*.xls*"
        .Show
    End With
    
    ' 遍历所有选中的文件
    For FileIdx = 1 To TargetFiles.SelectedItems.Count
        Set Databook = Workbooks.Open(TargetFiles.SelectedItems(FileIdx))
        
        ' 处理Staff工作表
        With Databook.Worksheets("Staff")
            .Unprotect
            .AutoFilterMode = False
            .Range("A3:O1000").Copy
        End With
        ' 粘贴到汇总表Staff页
        MasterWB.Sheets("Staff").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
        
        ' 只有Managers工作表存在时才处理
        If SheetExists("Managers", Databook) Then
            With Databook.Worksheets("Managers")
                .Unprotect
                .AutoFilterMode = False
                .Range("A3:P5000").Copy
            End With
            ' 粘贴到汇总表Managers页
            MasterWB.Sheets("Managers").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
        End If
        
        ' 关闭当前数据文件，清除剪贴板
        Databook.Close False
        Application.CutCopyMode = False
    Next
    
    ' 恢复Excel默认配置
    Application.DisplayAlerts = True
    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.AskToUpdateLinks = True
    Application.EnableEvents = True
End Sub

注意事项 #

• 运行宏前请提前打开Master Review - Test File工作簿，否则会触发找不到工作簿的报错
• 如果部分文件也可能不存在Staff工作表，可以仿照Managers的判断逻辑，给Staff的处理代码也套一层存在性判断

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