Exclude sheets from list

Question

I have the following code which will create a list for all the new sheets that I'm adding to the workbook except for the original tabs. However, I would like to exclude any tabs that end with RED and BAR. Below is my code:

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

I tried "*RED" and "*BAR" but it still included them on the list when they were visible, any ideas?

`UCase(Right((ws.Name),3)) = "RED"` or `UCase(Right((ws.Name),3)) = "BAR"` is the condition to check. — Gokhan Aycan, Aug 17 '21 at 20:03
`If Right(ws.name,3) = "BAR" or Right(ws.name,3) = "RED" Then`? You may also benefit from [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — cybernetic.nomad, Aug 17 '21 at 20:03
I had to play with it a bit but this is what finally worked: — CarmenV, Aug 17 '21 at 20:47

score 0 · Answer 1 · answered Aug 17 '21 at 21:19

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

Exclude sheets from list

1 Answers1