Excel VBA生成工作表列表时如何排除后缀为RED、BAR的工作表

VBA的Select Case语句默认执行精确文本匹配，直接在Case后写带*的通配符不会触发模糊匹配，因此你之前的后缀排除规则没有生效。

将原代码的Select Case ws.Name调整为Select Case True，通过Like运算符触发通配符匹配，新增后缀排除规则即可，修改后的完整代码如下：

Sub ListShts()
    Dim ws As Worksheet
    Dim j As Long   

    j = 2

    Sheets("Est List").Visible = True
    Sheets("Est List").Select

    ActiveSheet.Unprotect Password:="zxc"

    For Each ws In Worksheets
    ' 调整为布尔值匹配，支持通配符规则
    Select Case True
    ' 原有精确匹配的排除工作表，加上新增的后缀通配符排除规则
    Case ws.Name = "Summary Cover", ws.Name = "Summary RED", ws.Name = "Summary BAR", _
    ws.Name = "CT-T-LINES", ws.Name = "CT-T-STATION", ws.Name = "CT-D-LINES", ws.Name = "CT-D-STATION", _
    ws.Name = "EMA-T-LINES", ws.Name = "EMA-T-STATION", ws.Name = "EMA-D-LINES", ws.Name = "EMA-D-STATION", _
    ws.Name = "WMA-T-LINES", ws.Name = "WMA-T-STATION", ws.Name = "WMA-D-LINES", ws.Name = "WMA-D-STATION", _
    ws.Name = "PNH-T-LINES", ws.Name = "PNH-T-STATION", ws.Name = "PNH-D-LINES", ws.Name = "PNH-D-STATION", _
    ws.Name Like "*RED", ws.Name Like "*BAR"
    ' 满足以上任意条件的工作表都直接跳过，不录入列表

    Case Else
    If ws.Visible = xlSheetVisible Then
    Cells(j, 7).Value = ws.Name
    j = j + 1

    End If
    End Select

    Next ws

    Range("G2").Select

    Selection.Delete Shift:=xlUp

    Range("G1").Select
    ActiveSheet.Protect Password:="zxc"

    Sheets("How to Use").Activate

End Sub

• 把原来按工作表名称精确匹配的Select Case ws.Name改为Select Case True，支持多条件混合判断
• 新增两条通配符匹配规则ws.Name Like "*RED"、ws.Name Like "*BAR"，所有名称以RED、BAR结尾的工作表都会被自动排除
• 原有排除列表逻辑完全保留，不会影响原有功能，如果你想精简代码，也可以把已经被后缀规则覆盖的"Summary RED"、"Summary BAR"从排除列表中删除，运行效果一致

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