XL 2016 Sommaire dynamique avec onglet masqué

Private Sub Worksheet_Activate()
Dim i As Integer
Dim Nf As String
    [A6:A100].ClearContents
    For i = 2 To Sheets.Count
        If Sheets(i).Name <> "Feuil2" Then
            Nf = Sheets(i).Name
            ActiveSheet.Hyperlinks.Add Anchor:=Cells(i + 6, 1), Address:="", SubAddress:="'" & _
            Nf & "'" & "!A1", TextToDisplay:=Nf
        End If
    Next i
    [A6:A100].Sort Key1:=Range("A6"), Order1:=xlAscending, Header:=xlGuess
End Sub

Private Sub Worksheet_Activate()
Dim i As Integer
Dim Nf As String
    [A6:A100].ClearContents
    For i = 2 To Sheets.Count
        If Sheets(i).Visible = True Then
            Nf = Sheets(i).Name
            ActiveSheet.Hyperlinks.Add Anchor:=Cells(i + 6, 1), Address:="", SubAddress:="'" & _
            Nf & "'" & "!A1", TextToDisplay:=Nf
        End If
    Next i
    [A6:A100].Sort Key1:=Range("A6"), Order1:=xlAscending, Header:=xlGuess
End Sub

Private Sub Worksheet_Activate()
Dim i As Integer
Dim Nf As String
    [A6:A100].ClearContents
    For i = 2 To Sheets.Count
        If Sheets(i).Visible Then
            Nf = Sheets(i).Name
            ActiveSheet.Hyperlinks.Add Anchor:=Cells(i + 6, 1), Address:="", SubAddress:="'" & _
            Nf & "'" & "!A1", TextToDisplay:=Nf
        End If
    Next i
    [A6:A100].Sort Key1:=Range("A6"), Order1:=xlAscending, Header:=xlGuess
End Sub

Private Sub Worksheet_Activate()
Dim i As Integer
Dim Nf As String
Dim Sh As Worksheet
i = 7
    [A6:A100].ClearContents
    For Each Sh In ThisWorkbook.Worksheets
        If Sh.Visible And Sh.Name <> Me.Name Then
            Nf = Sh.Name
            i = i + 1
            ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 1), Address:="", SubAddress:="'" & _
            Nf & "'" & "!A1", TextToDisplay:=Nf
        End If
    Next Sh
    [A6:A100].Sort Key1:=Range("A6"), Order1:=xlAscending, Header:=xlGuess
End Sub