Index Sheet of Sheets (Auto Create)

[mole999]

I have looked at a number of methods to pull the Tab Names to an index sheet (skip over the code)

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

Sub Sheetnames()
Dim cells
Dim i
Columns(5).Insert
For i = 1 To Sheets.Count
cells(i, 1) = Sheets(i).Name
Next i
End Sub


Sub ListWorkSheetNames()
Dim Sheetnames
Sheetnames = Sheets.Count
Sheets.Add
ActiveSheet.Name = “SheetList”
Sheets(”SheetList”).Move after:=Sheets(Sheetnames + 1)
For i = 1 To Sheetnames
Range(”A” & i) = Sheets(i).Name
Next i
End Sub

Sub ListSheetNames()
Dim wSheet As Worksheet
Range("a:a").ClearContents
For Each wSheet In ThisWorkbook.Worksheets
  If wSheet.Name <> "My_Sheet" Then cells(Rows.Count, "a").End(xlUp)(2).Value = wSheet.Name
Next wSheet
End Sub

Each has its merits, yet I need to place an auto-updating (worksheet activate) starting in cell F6, and I have failed to find a way to place the start point, and I need to make these as hyperlinks, I'm expecting to get to at least three hundred sheets so its got to be reliable.

Its probably quite simple but I can't see a resolution

 

[hiker95]

mole999,

See if he following link will help you:

Create a menu item for a list of worksheets - by Tom Urtis
http://www.atlaspm.com/toms-tutoria...zing-your-rightclick-menu-to-list-run-macros/

 

[mole999]

Thanks, but unless I missed something this lists the macros, and my example above showed the ones I had looked at.

I need the sheet names on an index page and must start it somewhere other than A1 (I'm using index and match either side of my manual list, with explanation and subtotal above)

any other above would be good, just need to position them so they run one under another !!

 

[mole999]

This starts to work for me, now to tidy

Sub createindex()
    Application.DisplayAlerts = False
    Columns("E:E").ClearContents
    On Error Resume Next
    'sheets("index").delete
    On Error GoTo 0
    'Set myindex = Sheets.Add(Sheets(1))
    'myindex.Name = "Index"
    irow = 5    '1
    For Each thesheet In ThisWorkbook.Sheets
        If irow = 5 Then
            cells(irow, 5).Value = "INDEX"
        Else
            ActiveSheet.Hyperlinks.Add anchor:=cells(irow, 5) _
                                     , Address:="", SubAddress:= _
                                       thesheet.Name & "!B2", TextToDisplay:=thesheet.Name
                                       'The goto page and cell reference link
        End If
        irow = irow + 1
    Next
End Sub

 

[hiker95]

mole999,

Sorry about the link - wrong one.

Create a menu item for a list of worksheets - by Tom Urtis
http://www.mrexcel.com/forum/excel-questions/398894-creating-menu-worksheets.html