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

 

[C Moore]

InStr function?

 

[OV1]

InStr function?

I'm sorry don't know how to use that code

 

[OV1]

I'm sorry don't know how to use that code

I can create a list but need to skip some sheet name that contains alt

 

[jolivanes]

Would be nice if you could back up to your first post and use code tags so it becomes readable.

So what is your question?

 

[jolivanes]

You asked to create a list of sheet names not including sheet names that contain "alt" in the name.
Names will go in Column C of the Active Sheet.
This will do that

Sub No_Alt()
Dim sht As Worksheet, i As Long
i = 1
    For Each sht In ThisWorkbook.Worksheets
        If Not sht.Name Like "*alt*" Then Cells(i, 3).Value = sht.Name: i = i + 1
    Next sht
End Sub

 

[jolivanes]

Sub Using_Select_Case()
Dim ws As Worksheet, sht1 As Worksheet, i As Long
Set sht1 = Worksheets("Sheet1")
i = 0
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name Like "*alt*" And ws.Visible = xlSheetVisible Then
    Select Case ws.Name
        Case Is = "Data", "Sheet2", "Sheet5"    '<----- Other Sheets to be excluded from the list
            Case Else
            i = i + 1
        sht1.Cells(i, 3).Value = ws.Name    '<----- Names starting at C1
    End Select
End If
Next ws
Application.ScreenUpdating = True
End Sub

 

[OV1]

Sub Using_Select_Case()
Dim ws As Worksheet, sht1 As Worksheet, i As Long
Set sht1 = Worksheets("Sheet1")
i = 0
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name Like "*alt*" And ws.Visible = xlSheetVisible Then
    Select Case ws.Name
        Case Is = "Data", "Sheet2", "Sheet5"    '<----- Other Sheets to be excluded from the list
            Case Else
            i = i + 1
        sht1.Cells(i, 3).Value = ws.Name    '<----- Names starting at C1
    End Select
End If
Next ws
Application.ScreenUpdating = True
End Sub

Great that works, never though that code would be shorter and easy.
Thanks for that
now since the code changed, how can I make it hyperlink

 

[OV1]

Great that works, never though that code would be shorter and easy.
Thanks for that
now since the code changed, how can I make it hyperlink

Never mind, I got it thank you very much for your help

 

[OV1]

Thank you for the code.
it works great but would be possible to do two separate list in the same TOC one without alt and another with only alt?
This is what I got.

Sub TOC_List()

Dim ws As Worksheet, shtTOC As Worksheet, e As Long, i As Long
Set shtTOC = Worksheets("TOC")
i = 0
e = 0
Application.ScreenUpdating = False

    'Clear Cells
    shtTOC.Cells.Clear

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name Like "*alt*" And ws.Visible = xlSheetVisible Then
    Select Case ws.Name
        Case Is = "DATA", "Scope", "TOC"    '<----- Other Sheets to be excluded from the list
            Case Else
            i = i + 1
            shtTOC.Hyperlinks.Add Cells(i, 3), _
                                  Address:="", _
                                  SubAddress:="'" & ws.Name & "'!A1", _
                                  TextToDisplay:=ws.Name
            shtTOC.Cells(i, 3).Value = ws.Name    '<----- Names starting at C1
    End Select
End If
If ws.Name Like "*alt*" And ws.Visible = xlSheetVisible Then
    Select Case ws.Name
            Case Else
            e = e + 1
            shtTOC.Hyperlinks.Add Cells(e, 4), _
                                  Address:="", _
                                  SubAddress:="'" & ws.Name & "'!A1", _
                                  TextToDisplay:=ws.Name
            shtTOC.Cells(e, 4).Value = ws.Name    '<----- Names starting at C1
    End Select
End If
Next ws
Application.ScreenUpdating = True

End Sub