Zack Baresse's TOC - is it possible to get more info on TOC index than sheet name hyperlink

[donh]

I am using Zack Baresse's Table of Contents index code (which is very very slick) but am in need of a little more information on the index page.

Starting with worksheet 5 to the end of the workbook is it possible to add to the code to either copy/paste or create a link (which ever is easiest) for these cells:

C9, K13, D23, E23, F23, D24, E24, F24, F25, P28

to be put on the index page starting with column D and going across for each worksheet?

here is Zack Baresse's Table of Contents code

Sub eCreateMarksiNDEX()
    Application.ScreenUpdating = False
    If ActiveWorkbook Is Nothing Then
        MsgBox "You must have a workbook open first!", vbInformation, "No Open Book"
        Exit Sub
    End If

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False

        Dim ws As Worksheet, _
            ct As Chart, _
            shtName As String, _
            nrow As Long, _
            tmpCount As Long, _
            i As Long, _
            numCharts As Long

        nrow = 3
        i = 1
        numCharts = ActiveWorkbook.Charts.Count

        On Error GoTo hasSheet
        Sheets("INDEX").Activate
        If MsgBox("You already have an Index page.  Would you like to overwrite it?", _
                  vbYesNo + vbQuestion, "Replace Index page?") = vbYes Then GoTo createNew
        Exit Sub

hasSheet:
        Sheets.Add Before:=Sheets(1)
        GoTo hasNew

createNew:
        Sheets("INDEX").Delete
        GoTo hasSheet

hasNew:
        tmpCount = ActiveWorkbook.Charts.Count
        If tmpCount > 0 Then tmpCount = 1
        ActiveSheet.Name = "INDEX"

        With Sheets("INDEX")
            .Cells.Interior.ColorIndex = 36
            With .Range("B2")
                .Value = "INDEX"
                .Font.Bold = True
                .Font.Name = "Tahoma"
                .Font.Size = "24"
            End With
        End With

        For Each ws In ActiveWorkbook.Worksheets
            nrow = nrow + 1
            With ws
                shtName = ws.Name
                With Sheets("INDEX")
                    .Range("B" & nrow).Value = nrow - 3
                    '.Range("B" & nrow).Font.Size = "14"
                    .Range("C" & nrow).Hyperlinks.Add _
                        Anchor:=Sheets("INDEX").Range("C" & nrow), Address:="#'" & _
                                                                            shtName & "'!A1", TextToDisplay:=shtName
                .Range("C" & nrow).HorizontalAlignment = xlLeft
                    '.Range("C" & nrow).Font.Size = "14"
                End With
            End With

        Next ws

        End_Row = Range("b" & Rows.Count).End(xlUp).Row

        Range("b4:c" & End_Row).Font.Size = "14"

        If numCharts <> 0 Then
            For Each ct In ActiveWorkbook.Charts
                nrow = nrow + 1
                shtName = ct.Name
                With Sheets("INDEX")
                    .Range("B" & nrow).Value = nrow - 3
                    .Range("C" & nrow).Value = shtName
                    .Range("C" & nrow).HorizontalAlignment = xlLeft
                End With
            Next ct
        End If

        With Sheets("INDEX")
            With .Range("B2:G2")
                .MergeCells = True
                .HorizontalAlignment = xlLeft
            End With

            With .Range("C:C")
                .EntireColumn.AutoFit
                .Activate
            End With
            .Range("B4").Select
        End With

        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    
    ' YOU MIGHT LIKE TO USE VERSIONS OF THE CODE BELOW
'    Range("B18:C36").Cut Destination:=Range("E4:F22")
'    Range("B37:C58").Cut Destination:=Range("I4:J25")
'    Range("I4:K4").Cut Destination:=Range("E23:G23")
'    Range("I5:K24").Cut Destination:=Range("I4:K23")
'
'    Columns("a:a").ColumnWidth = 3
'    Columns("b:b").ColumnWidth = 4
'    Columns("c:c").ColumnWidth = 15
'    Columns("d:d").ColumnWidth = 3
'    Columns("e:e").ColumnWidth = 4
'    Columns("F:F").ColumnWidth = 26
'    Columns("g:g").ColumnWidth = 3
'    Columns("h:h").ColumnWidth = 0
'    Columns("i:i").ColumnWidth = 4
'    Columns("j:j").ColumnWidth = 22
'    Rows("3:3").RowHeight = 0
'    Rows("2:2").RowHeight = 24
'    Rows("1:1").RowHeight = 2
'    Range("a2").Activate

    Application.ScreenUpdating = True

End Sub

Any help or ideas would be greatly appreciated

Thanks

Don

 

[GlennUK]

And what have you tried so far? Have you done any experiments, or attempts at this yourself, in any way?

 

[donh]

I can only get it to work for the first worksheet (which would be #5) - - - I don't know how to write the code to make it advance to the next tabs which could be any from 5 to 150 or so

I spent most of the afternoon going through threads and was not able to find one to help which is why I put it out there

Sorry

 

[GlennUK]

I can only get it to work for the first worksheet (which would be #5) - - - I don't know how to write the code to make it advance to the next tabs which could be any from 5 to 150 or so

I spent most of the afternoon going through threads and was not able to find one to help which is why I put it out there

Sorry

But the code you posted already loops through all sheets!?!?

 

[donh]

I know it loops through otherwise the hyperlinks would not be there for each worksheet

I messed around with this piece of the code:

.Range("C" & nrow).Hyperlinks.Add _
Anchor:=Sheets("INDEX").Range("C" & nrow), Address:="#'" & _
shtName & "'!A1", TextToDisplay:=shtName

trying a lot of different things to try and capture what was needed off to this sheet but could not get anything to work - - - that's why I went to this board for help

 

[GlennUK]

Like having
.Range("D" & nrow).Formula="=" & shtName & "'!C9"
and so on?

 

[donh]

Almost - - - I also changed the

TextToDisplay:=shtName to TextToDisplay:=C9

Thinking that it would give me values - - -

Looks like if I had left off the TextToDisplay I might have had it

Thanks