Macro conseguir lista de todas las hojas excel

Prueba con esta macro

Option Base 1
Sub agregar_hojas()
contar = Worksheets.Count
ReDim matriz(contar)
x = 1
For Each hoja In Worksheets
    nombre = hoja.Name
    matriz(x) = nombre
    x = x + 1
Next hoja
Sheets(nombre).Select
Set datos = Range("a1").Resize(contar, 1)
Range(datos.Address) = WorksheetFunction.Transpose(matriz)
End Sub

Te anexo la macro:

Sub Nombres_Hojas()
    For i = 1 To Sheets.Count
        Sheets(Sheets.Count).Range("A" & i).Value = Sheets(i).Name
    Next
End Sub

[Sal u dos, no olvides valorar la respuesta.