从列表中排除工作表

Question

我有以下代码，这将创建一个列表的所有新工作表，我添加到工作簿除了原来的选项卡。但是，我想排除任何以RED和BAR结尾的选项卡。下面是我的代码:

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 ws.Name
Case "Summary Cover", "Summary RED", "Summary BAR", _
"CT-T-LINES", "CT-T-STATION", "CT-D-LINES", "CT-D-STATION", _
"EMA-T-LINES", "EMA-T-STATION", "EMA-D-LINES", "EMA-D-STATION", _
"WMA-T-LINES", "WMA-T-STATION", "WMA-D-LINES", "WMA-D-STATION", _
"PNH-T-LINES", "PNH-T-STATION", "PNH-D-LINES", "PNH-D-STATION" _
' I want to exclude all the tabs that have RED or BAR at the end.  
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

我试过"*红色"one_answers"* BAR"但当它们可见时，它仍然将它们列入列表，有什么想法吗?

Answer 1

Sub ListShts1()
Dim ws As Worksheet
Dim j As Long
j = 2
Sheets("List").Visible = True
Sheets("List").Select
ActiveSheet.Unprotect Password:="zxc"
For Each ws In Worksheets   
If UCase(Right((ws.Name), 3)) = "RED" Or UCase(Right((ws.Name), 3)) = "BAR" _
Or ws.Name = "Summary Cover" Or ws.Name = "List" Then
'do nothing
Else
If ws.Visible = xlSheetVisible Then
Cells(j, 7).Value = ws.Name
j = j + 1
End If
End If
Next ws
ActiveSheet.Protect Password:="zxc"
End Sub