XL 2013 (resolu) récuperer nom onglet dans cellule

=DROITE(CELLULE("nomfichier";A1);NBCAR(CELLULE("nomfichier";A1))-TROUVE("]";CELLULE("nomfichier";A1)))

Sub Recuperation()
Dim ws As Worksheet
c = 1
    For Each ws In ActiveWorkbook.Worksheets
       If ws.Name <> "SYNTHESE" Then

        With Sheets("SYNTHESE")
            .Cells(1, c) = ws.Name
            .Range(Cells(1, c), Cells(1, c + 2)).Merge:
            .Range(Cells(1, c), Cells(1, c + 2)).HorizontalAlignment = xlCenter
            .Range(Cells(1, c), Cells(1, c + 2)).Interior.Color = RGB(242, 242, 242)
        c = c + 3
        End With
        End If
    Next
End Sub

Sub Recuperation()
Dim ws As Worksheet
c = 1
    For Each ws In ActiveWorkbook.Worksheets
       If ws.Name <> "SYNTHESE" Then

        With Sheets("SYNTHESE")
            .Cells(1, c) = ws.Name
            .Range(Cells(1, c), Cells(1, c + 2)).Merge:
            .Range(Cells(1, c), Cells(1, c + 2)).HorizontalAlignment = xlCenter
            .Range(Cells(1, c), Cells(1, c + 2)).Interior.Color = RGB(242, 242, 242)
        c = c + 3
        End With
        End If
    Next
End Sub