VBA合并工作簿时如何跳过xlSheetHidden/xlSheetVeryHidden隐藏表

需求说明 #

• 合并当前工作簿内所有非隐藏工作表，跳过状态为xlSheetHidden、xlSheetVeryHidden的隐藏工作表
• 合并时仅粘贴单元格值，不携带格式、公式

原有代码失效原因 #

之前尝试的判断逻辑不生效，核心是3个语法/逻辑错误：

1. 直接写If .Visible = xlSheetVisible Then时，.前没有绑定具体的工作表对象，属于无效的属性引用，判断逻辑根本不会执行
2. 遍历工作表时用For Each Sheets In ActiveWorkbook.Worksheets写法，把循环变量命名为VBA内置的工作表集合名Sheets，产生命名冲突；后续判断写的是Sheet.Visible，和循环变量名不匹配，判断完全不生效
3. 初始代码先把所有工作表名称（含隐藏表）全量存入数组，后续循环时没有做二次可见性校验，自然会把隐藏表内容也合并进去；另外新建的合并表如果没有做排除，还会出现合并表自我复制的内容重复问题

修正后可直接运行的代码 #

Sub Merge_Sheets()
    Dim ws As Worksheet
    Dim combinedWs As Worksheet
    Dim copyRng As Range
    Dim currentRow As Long
    
    ' 关闭屏幕更新提升运行速度
    Application.ScreenUpdating = False
    
    ' 先删除已存在的同名合并表，避免重名报错
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("Combined Sheet").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    ' 新建合并表
    Set combinedWs = ThisWorkbook.Worksheets.Add
    combinedWs.Name = "Combined Sheet"
    currentRow = 1 ' 从第一行开始写入
    
    ' 遍历所有工作表
    For Each ws In ThisWorkbook.Worksheets
        ' 跳过合并表自身 + 所有隐藏/深度隐藏的表
        If ws.Name <> combinedWs.Name And ws.Visible = xlSheetVisible Then
            Set copyRng = ws.UsedRange
            ' 仅粘贴值
            copyRng.Copy
            combinedWs.Cells(currentRow, 1).PasteSpecial Paste:=xlPasteValues
            ' 行号偏移，空一行分隔不同表的内容（不需要空行可以去掉+1）
            currentRow = currentRow + copyRng.Rows.Count + 1
        End If
    Next ws
    
    ' 清除剪贴板，恢复屏幕更新
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    MsgBox "工作表合并完成", vbInformation
End Sub

代码说明 #

• 直接绑定Worksheet类型对象遍历，避免索引取值的偏移错误，可见性判断直接绑定到遍历到的工作表对象，不会出现引用失效问题
• 运行前自动删除已存在的同名合并表，避免重名报错
• 自动跳过合并表自身，不会出现内容重复
• 新增屏幕更新开关，大文件合并时运行速度更快
• 保留原代码空行分隔不同工作表内容的逻辑，如果不需要可以把currentRow = currentRow + copyRng.Rows.Count + 1里的+1删掉

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