Formula for copying the tab name?

[jasondrew]

Hi there,

I was wondering if anyone can help - I'm after a formula that will copy onto a summary page the 'tab' name of each of my worksheets?

It would save me a whole lot of work if one exists!!

thanks, Jason

 

[VoG]

This will create an index sheet with hyperlinks

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

 

[Michael M]

Also have a look here at Jonmos post.
You will have to change the sheet name in the code to suit.


http://www.mrexcel.com/forum/showthread.php?t=322067

 

[jasondrew]

Hi there,

Many thanks for your help - but i'm struggling to know what to do with this equation?

I can't seem to make it work? Help would be great

Sorry and thanks

 

[VoG]

If you mean my code, press ALT + F11 to open the Visual Basic Editor, select Module from the Insert menu then paste the code into the white space on the right.

Press ALT + Q to close the code window, press ALT + F8, click on IDX then click the Run button.

 

[jasondrew]

Brilliant, that works a treat - many thanks for all your help

Jason