Copy Sheet Name

[MrVillareal]

Hi MrExcel Users,

I have many sheets in 1 workbook and I want the sheet name in 1 new sheet starting at A1. Can anyone help me using VBA to copy sheet name?

Thank you in advance and appreciate all helps.

Best regards,
Marvin

 

[VoG]

This will add an index sheet with hyperlinks to the other sheets

Sub IDX()
Dim ws As Worksheet, i As Integer
Worksheets.Add(before:=Worksheets(1)).Name = "Index"
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Index" Then
        i = i + 1
        Sheets("Index").Range("A" & i).Value = ws.Name
        Sheets("Index").Hyperlinks.Add Anchor:=Range("A" & i), Address:="", SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
    End If
Next ws
Sheets("Index").Columns("A").AutoFit
End Sub

 

[MrVillareal]

Hi Vog,

Thats ridiculously perfect!..will it always work if I will add another sheet? I mean will the first index will update after I add another sheet and run it again?

Thank you so much this is perfect

 

[VoG]

If you add another sheet you will need to delete the Index sheet then run the code again.

 

[MrVillareal]

Thank you very much for this great help