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

CodePudding user response：

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:

CodePudding user response：

List Certain Worksheets

Option Explicit

Sub ListSheetsSkip()
    ' The worksheets in the list will be SKIPPED.
    
    Const dSkipSheetsList As String _
        = "Template,Reference Data 1,Reference Data 2"
    Const dfCol As String = "A"
    Const dfRow As Long = 1
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim dws As Worksheet: Set dws = wb.Worksheets("Summary")
    Dim dRow As Long: dRow = dfRow
    
    Dim dSkipSheets() As String: dSkipSheets = Split(dSkipSheetsList, ",")
    
    Dim sws As Worksheet
     
    For Each sws In wb.Worksheets
        If IsError(Application.Match(sws.Name, dSkipSheets, 0)) Then
            dws.Cells(dRow, dfCol).Value = sws.Name
            dRow = dRow   1
        End If
    Next sws
 
    MsgBox "Worksheets found: " & dRow - dfRow, vbInformation
 
End Sub

Sub ListSheetsPick()
    ' The worksheets in the list will be PICKED.
    
    Const dPickSheetsList As String = "Summary,Expenses,Revenue,Advisors"
    Const dfCol As String = "A"
    Const dfRow As Long = 1
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim dws As Worksheet: Set dws = wb.Worksheets("Summary")
    Dim dRow As Long: dRow = dfRow
    
    Dim dPickSheets() As String: dPickSheets = Split(dPickSheetsList, ",")
    
    Dim sws As Worksheet
     
    For Each sws In wb.Worksheets
        If IsNumeric(Application.Match(sws.Name, dPickSheets, 0)) Then
            dws.Cells(dRow, dfCol).Value = sws.Name
            dRow = dRow   1
        End If
    Next sws
 
    MsgBox "Worksheets found: " & dRow - dfRow, vbInformation

End Sub

Sub ListSheetsTabColor()
' The worksheets with a black tab color will be SKIPPED.
    
    Const dfCol As String = "A"
    Const dfRow As Long = 1
    Const NotTabColor As String = "0" ' "0"-Black, "255"-Red, "False"-NoColor
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim dws As Worksheet: Set dws = wb.Worksheets("Summary")
    Dim dRow As Long: dRow = dfRow
    
    Dim sws As Worksheet
     
    For Each sws In wb.Worksheets
        If CStr(sws.Tab.Color) <> NotTabColor Then
            dws.Cells(dRow, dfCol).Value = sws.Name
            dRow = dRow   1
        End If
    Next sws
 
    MsgBox "Worksheets found: " & dRow - dfRow, vbInformation

End Sub