Help with VBA edit

[richard11153]

I have some code in a workbook i am making that gives me an "Auto Updating" table of contents" (TOC).
It works well, but when I added a "1st page cover" the TOC still shows as the 2nd page. (See worksheet attached)
I want the TOC page to be the 2nd page, and show as the 2nd page on the TOC
I also never want the page "Radon" to show in the table of contents.
Can someone with VBA skills help me with this.
Any help is very much appreciated!!
(The Code is below, but I can also e-mail you the work book, if that is possible)
Thank you / Richard

Option Explicit


'=========================================================================================
'=========================================================================================


Private Sub Worksheet_Activate()

'   Runs every time the sheet is activated by the user.

'   Create Table of Contents
    Call TOC_List

End Sub


'=========================================================================================
'=========================================================================================


Private Sub TOC_List()

'   Create Table of Contents on this TOC sheet

    Const bLIST_HIDDEN_SHEETS   As Boolean = False
    Const iMAXIMUM_ROWS         As Integer = 25
    Const sHEADER_CELL          As String = "C8"

    Dim rContentsCells          As Range
    Dim rHeaderCell             As Range
    Dim iSheetNo                As Integer
    Dim wksTOC                  As Worksheet
    Dim wks                     As Worksheet

    Application.ScreenUpdating = False

'       Set variables
        Set wksTOC = Me '   can change to a worksheet ref if using in a regular code module
        
        Set rHeaderCell = wksTOC.Range(sHEADER_CELL)
        
        Set rContentsCells = Range(rHeaderCell.Offset(0, 0), _
                                    rHeaderCell.Offset(iMAXIMUM_ROWS, 1))

'       Clear Cells
        rContentsCells.ClearContents

'       Create TOC list
        iSheetNo = 1

        With wksTOC

'           Add TOC sheet at first item in list
            rHeaderCell.Offset(iSheetNo).Value = iSheetNo

            Call InsertHyperlink(rHeaderCell:=rHeaderCell, _
                                 iSheetNo:=iSheetNo, _
                                 wks:=wksTOC)

            iSheetNo = iSheetNo + 1

            For Each wks In ThisWorkbook.Worksheets

                If wks.Name <> wksTOC.Name Then

'                   Skipping hidden sheets can be toggled in the variable above
                    If wks.Visible = xlSheetVisible Or _
                       bLIST_HIDDEN_SHEETS = True Then

                        rHeaderCell.Offset(iSheetNo).Value = iSheetNo

                        Call InsertHyperlink(rHeaderCell:=rHeaderCell, _
                                             iSheetNo:=iSheetNo, _
                                             wks:=wks)

                        iSheetNo = iSheetNo + 1

                    End If

                End If

            Next wks

'           Turn filters off
            If .AutoFilterMode = True Then
                .Cells.AutoFilter
            End If

        End With                '   With wksTOC

        With rContentsCells

'           Apply filters
            .AutoFilter

'           Formatting
            .Font.Italic = True

            

        End With                '   With rContentsCells

    Application.ScreenUpdating = True

End Sub


'=========================================================================================
'=========================================================================================


Private Sub InsertHyperlink(rHeaderCell As Range, iSheetNo As Integer, wks As Worksheet)

    Dim wksTOC As Worksheet

    Set wksTOC = rHeaderCell.Parent

    With wksTOC

        .Hyperlinks.Add Anchor:=rHeaderCell.Offset(iSheetNo, 1), _
                        Address:=vbNullString, _
                        SubAddress:="'" & wks.Name & "'!A1", _
                        TextToDisplay:=wks.Name

    End With

End Sub
[code]

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Need a small edit for some VBA code that runs a table of contents
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.