如何修改VBA代码实现内容粘贴到除前7个工作表外的所有表

你可以直接在原有代码基础上新增前7个工作表的索引判断即可实现需求，无需大幅调整原有逻辑，修改后的完整代码如下：

Dim aWshExcluded As Variant, vWshExc As Variant
aWshExcluded = Array("Exclude(1)", "Exclude(2)")
Dim WshSrc As Worksheet
Dim WshTrg As Worksheet

Rem Set Source Worksheet
Set WshSrc = ThisWorkbook.Worksheets(2)
Application.ScreenUpdating = 0

    Rem Process All Worksheets
    For Each WshTrg In WshSrc.Parent.Worksheets

        Rem Exclude Worksheet Source
        If WshTrg.Name <> WshSrc.Name Then
              
            Rem 新增：排除前7个工作表
            If WshTrg.Index <= 7 Then GoTo NEXT_WshTrg

            Rem Validate Worksheet vs Exclusion List
            For Each vWshExc In aWshExcluded
                If WshTrg.Name = vWshExc Then GoTo NEXT_WshTrg
            Next

            Rem Process Worksheet Target
            With WshTrg.Cells
                WshSrc.Cells.Copy
                .PasteSpecial Paste:=xlPasteAll    'Everything is pasted.
                Application.CutCopyMode = False
                Application.Goto .Cells(1), 1
            End With
        End If

NEXT_WshTrg:
 
    Next

Application.Goto Worksheets("Main").Cells(1), 1
Application.ScreenUpdating = 1
End Sub

调整说明 #

• 新增的索引判断逻辑通过工作表的默认排序序号（Index属性）识别前7个工作表，匹配就直接跳过复制操作，不需要手动录入前7个表的名称
• 如果后续需要调整排除的工作表数量，直接修改If WshTrg.Index <= 7中的数字7即可

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