处理活动工作簿的工作表，不包括指定范围内列表中的工作表：

将A1：A6地址修改为排除列表的位置。如果列表位于另一个命名工作表上，请修改Sheet1部分。

Sub ProcessWorksheets()
    
    'declarations
    Dim ws As Worksheet
    Dim exceptionlist As Range
    
    'define range that contains exceptions
    Set exceptionlist = ActiveWorkbook.Worksheets("Sheet1").Range("A1:A6")
    
    'cycle through each worksheet
    For Each ws In ThisWorkbook.Worksheets
        'if ws.name does not exist in the range
        If Application.CountIf(exceptionlist, ws.Name) = 0 Then
            'do something with ws
            Debug.Print ws.Name
        Else
            'ignore it, do nothing
        End If
    Next

End Sub

你能做的就是创建一个名为Exclude的工作表。在A列的第一行写上Exclude。在后面写上你的其他工作表。最后，使用下面的代码。

Dim Lastrow As Integer
Lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row

Set rs = ws.Range("A1:A1" & Lastrow)

For Each ws In ThisWorkbook.Worksheets
    If Not rs.Find(ws.Name) Is Nothing Then
        'Your code
    End If
Next

循环工作表，排除列表中的工作表

• 在添加处理代码之前，请按原样测试此代码，以确保其工作正常，即包含正确的工作表。

Option Explicit

Sub CollectData()

    ' Define constants.
    Const LIST_WORKSHEET_ID = 1 ' rather use the (tab name), e.g. "Sheet1"
    Const LIST_FIRST_CELL As String = "Z2"

    ' Reference the workbook.
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Reference the list range.
    Dim lws As Worksheet: Set lws = wb.Sheets(LIST_WORKSHEET_ID)
    Dim lfCell As Range: Set lfCell = lws.Range(LIST_FIRST_CELL)
    Dim llCell As Range
    Set llCell = lws.Cells(lws.Rows.Count, lfCell.Column).End(xlUp)
    Dim lrg As Range: Set lrg = lws.Range(lfCell, llCell)
    
    Dim sws As Worksheet
    
    ' Loop through all worksheets...
    For Each sws In wb.Worksheets
        ' ... excluding the ones from the list:
        If IsError(Application.Match(sws.Name, lrg, 0)) Then
            
            ' Continue using the 'sws' variable , e.g.:
            Debug.Print sws.Name
        
        'Else ' it's one from the list; do nothing
        End If
    Next sws
    
    MsgBox "Data collected.", vbInformation
    
End Sub