none
Question about excluding specific named worksheets from a dynamic index RRS feed

  • Question

  • I'm using the following code to pull the names of my worksheets and create a dynamic index. I already have it set to ignore worksheets set to hidden, but I have two additional worksheets that I don't wish to be added to the index. I've tried several different ways to exclude them, but have had no success. I suspect I am using the incorrect syntax. The worksheets I want to exclude are named 100000 and 999999. Any advice on how to alter this code to ignore those specific worksheets would be appreciated.

    Private Sub Worksheet_Activate()
    Dim wSheet As Worksheet
    Dim n As Integer
    Dim calcState As Long, scrUpdateState As Long

    calcState = Application.Calculation
    Application.Calculation = xlCalculationManual
    scrUpdateState = Application.ScreenUpdating
    Application.ScreenUpdating = False

    n = 1

        With Me
            .Columns(1).ClearContents
            .Cells(1, 1) = "Inv #"
            .Cells(1, 1).Name = "Index"
        End With
        
        For Each wSheet In Worksheets
            If wSheet.Name <> Me.Name And wSheet.Visible = xlSheetVisible Then
                n = n + 1
                    With wSheet
                        .Range("O1").Name = "Start_" & wSheet.Index
                         .Hyperlinks.Add anchor:=.Range("O1"), Address:="", _
                        SubAddress:="Index", TextToDisplay:="Back to Index"
                    End With
                    
                    Me.Hyperlinks.Add anchor:=Me.Cells(n, 1), Address:="", _
                    SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
            End If
        Next wSheet
        
    Application.Calculation = calcState
    Application.ScreenUpdating = scrUpdateState
    End Sub

    • Edited by Z00P Monday, July 8, 2019 8:35 PM
    Monday, July 8, 2019 8:25 PM

Answers

  • Change the line

            If wSheet.Name <> Me.Name And wSheet.Visible = xlSheetVisible Then

    to

            If wSheet.Name <> Me.Name And wSheet.Name <> "100000" And wSheet.Name <> "999999" And wSheet.Visible = xlSheetVisible Then


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Z00P Monday, July 8, 2019 9:53 PM
    Monday, July 8, 2019 8:53 PM