Make a sheet name list (VBA) that contains specific text

[OV1]

Hello,

I hope someone helps me get the code right.

I create a sheet name list (VBA) but I want to skip sheet names that contain "alt"
here is my code
Sub TOC_List()
'Create Table of Contents on this TOC sheet

Dim ws As Worksheet
Dim wsTOC As Worksheet
Dim i As Long


Application.ScreenUpdating = False

'Set variables
Const bSkipHidden As Boolean = False 'Change this to True to list hidden sheets
Const sTitle As String = "A100"
Const sHeader As String = "A100"
Set wsTOC = Me 'can change to a worksheet ref if using in a regular code module
i = 1

'Clear Cells
Range("TableScope").Clear


'Create TOC list
With wsTOC.Range(sHeader)
' wsTOC
'Create list
For Each ws In ThisWorkbook.Worksheets
'Skip TOC sheet
If ws.Name <> Sheet2.Name Then
If ws.Name <> Sheet5.Name Then
If ws.Name <> Data.Name Then
if ws.Name <> sheet that contains "alt"


'Skipping hidden sheets can be toggled in the variable above
If bSkipHidden Or ws.Visible = xlSheetVisible Then
.Offset(i).Value = i
wsTOC.Hyperlinks.Add Anchor:=.Offset(i, 1), _
Address:="", _
SubAddress:="'" & ws.Name & "'!A1", _
TextToDisplay:=ws.Name
i = i + 1
Else
ws.Visible = xlSheetHidden
End If
End If
End If
End If

Next ws

End With

Application.ScreenUpdating = True

End Sub

 

[jolivanes]

Re: "one without alt and another with only alt"
What about the three other (Data, Scope, TOC) sheets?

Please don't quote. Refer to Post numbers and/or poster names.

 

[jolivanes]

This will give you the restricted sheet names in Column C and the "alt" Sheet names in Column D.

Sub Maybe_2()
Dim ws As Worksheet, noAlt, altSh
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name Like "*alt*" And ws.Visible = xlSheetVisible Then
            If ws.Name <> "Index" And ws.Name <> "Sheet1" And ws.Name <> "Sheet2" Then noAlt = noAlt & "," & ws.Name
                Else
            If InStr(ws.Name, "alt") <> 0 Then altSh = altSh & "," & ws.Name
        End If
    Next ws
noAlt = Application.Transpose(Split(Mid(noAlt, 2), ","))
altSh = Application.Transpose(Split(Mid(altSh, 2), ","))
Cells(1, 3).Resize(UBound(noAlt)) = noAlt
Cells(1, 4).Resize(UBound(altSh)) = altSh
End Sub