List sheet names in excel while skipping the unwanted sheets

January 20, 2022

How can a list of sheet names be generated in excel, while skipping the unwanted sheets?

I would like to list “Expenses”, “Revenue”, and “Advisors” (Green tabs). While skipping “Template”, “Reference Data 1”, and “Reference Data 2” (Black tabs).
I receive an error while using the code below.

All help and guidance is appreciated.

Sub List_Sheets()
'
' List_Sheets Macro
'
Dim ws As Worksheet
Dim x As Integer
 
x = 1 'Starting Row
 
For Each ws In Worksheets
    If InStr(ws.Name, "Template") Then 'Skip "Template" also skip "Referance data 1" and "Referance data 2"
        GoTo NextIteration
    Sheets("Summary").Cells(x, 1) = ws.Name 'Starting collunm 1 also know as A
    x = x + 1
NextIteration:
Next ws
 
End Sub

>Solution :

This part:

If InStr(ws.Name, "Template") Then 'Skip "Template" also skip "Referance data 1" and "Referance data 2"
        GoTo NextIteration
    Sheets("Summary").Cells(x, 1) = ws.Name 'Starting collunm 1 also know as A
    x = x + 1
NextIteration:

should become:

If InStr(ws.Name, "Template") > 0 or _
     InStr(ws.Name, "Reference") > 0  Then GoTo NextIteration
        
     Sheets("Summary").Cells(x, 1) = ws.Name 'Starting collunm 1 also know as A
     x = x + 1
NextIteration: