XL 2013 liste onglets visibles

Sub Liste_onglets_visibles()
Dim s As Object, a$(), n
For Each s In Sheets
    If s.Visible = xlSheetVisible Then
        ReDim Preserve a(n) 'base 0
        a(n) = s.Name
        n = n + 1
    End If
Next
'---restitution---
With [A1] '1ère cellule, à adapter
    .Resize(n) = Application.Transpose(a)
    .Offset(n).Resize(Rows.Count - n - .Row + 1).ClearContents 'RAZ en dessous
End With
End Sub

Sub Liste_onglets_visibles()
Dim dest As Range, w As Worksheet
Set dest = [A1] '1ère cellule de destination, à adapter
Application.ScreenUpdating = False
dest.Resize(Rows.Count - dest.Row + 1).Clear 'RAZ
For Each w In Worksheets
    If w.Visible = xlSheetVisible Then
        dest.Hyperlinks.Add dest, "", "'" & w.Name & "'!A1", TextToDisplay:=w.Name
        Set dest = dest(2)
    End If
Next
End Sub

Sub Liste_onglets_visibles()
Dim dest As Range, w As Worksheet, x$
Set dest = [A1] '1ère cellule de destination, à adapter
Application.ScreenUpdating = False
dest.Resize(Rows.Count - dest.Row + 1).Clear 'RAZ
For Each w In Worksheets
    If w.Visible = xlSheetVisible Then
        x = IIf(TypeName(Evaluate(w.Name & "!A1")) = "Range", "", "'")
        ActiveSheet.Hyperlinks.Add dest, "", x & w.Name & x & "!A1", TextToDisplay:=w.Name
        Set dest = dest(2)
    End If
Next
End Sub

Sub Liste_onglets_visibles()
Dim w As Worksheet, x$, a(), n&
Application.ScreenUpdating = False
With [A1] '1ère cellule de destination, à adapter
    .Resize(Rows.Count - .Row + 1).Clear 'RAZ
    For Each w In Worksheets
        If w.Visible = xlSheetVisible Then
            x = IIf(TypeName(Evaluate(w.Name & "!A1")) = "Range", "", "'")
            ReDim Preserve a(n) 'base 0
            a(n) = "=HYPERLINK(""#" & x & w.Name & x & "!A1"",""" & w.Name & """)"
            n = n + 1
        End If
    Next
    .Resize(n) = Application.Transpose(a) 'restitution
End With
End Sub