Excel - nombre hoja convertir a valor en un celda

hola amigos
no logro que el nombre de las hojas me aparezca en celdas en una hoja aparte.
Una vez tenga esto necesito que las formulas tipo ='nombre hoja'!A2 me cojan ese valor

Gracias
Juan

Macro para listar todas las hojas del libro

Sub NombreHojas()
'Pone los nombres de las hojas
Dim contador As Integer
For contador = 1 To Sheets.Count
With Sheets(1)
Cells(contador + 4, 6).Value = Sheets(contador).Name
End With
Next
End Sub

Salu2