Thread: How to generate a list of tab names in a summary sheet

Hi all,

was wondering if anyone could tell me how to automatically generate a list of tab names, possibly using VBA that also sorts the list either by...

(i) the order of which the list of tabs appears in the summary sheet or
(ii) alphabetically

I would appreciate any help and assistance. Is probably an easy query for you guys but I am 7 days into using VBA and learning lots with all your kind help!!!

Hi mate,

I have completed the task as per the instructions but it appears their is an error, as per the attached sheet.

I am quite confused, I have tried this before and it did not work for me.

Macros are enabled.

Thanks for your help!!!

You have extra spaces in your code that is not recognizing the line join (_).
Try this...

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long
l = 1

    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "INDEX"
        .Cells(1, 1).Name = "Index"
    End With

    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name Then
            l = l + 1
            With wSheet
                .Range("A1").Name = "Start_" & wSheet.Index
                .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
                SubAddress:="Index", TextToDisplay:="Back to Index"
            End With

            Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
            SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
        End If
    Next wSheet
End Sub

Sweet works a treat,

I have a summary sheet that will always be fixed according to budgets.

work 1, budget 1
work 2, budget 2

How do I get the tabs to fall within the order I specify without manually moving them?