Thread: Form table of contents for a sheet

Hello everyone,

I need a table of contents that will form acorrdind to specific text and show page numbers.

Looked all over internet, but there is only a posibility to make sheet table of contents and that is not the right solution for me.

I was thinking of a macto that would find specific text and copy it to the begginig of the sheet and hyperlink it, but have no idea how to put page numbers

So that is what i have so far:

Code:

Sub_TOC()
 Sheets("Sheet2").Select
    Cells.Find(What:="PRESENTATION", After:=ActiveCell, LookIn _
        :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Range("B78:D78").Select
    Selection.Copy
 Range("B35:E35").Select
    ActiveSheet.Paste

 ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "Sheet2!B78", TextToDisplay:="PRESENTATION"
End sub

Any solution to do this would be appreciated

You can try following example ...

Code:

Option Explicit
Dim strAdr As String
Dim wksTOC As Worksheet

Sub First_Finding_InWorksheet()
    
    Dim rng As Range
    Dim wks As Worksheet
    Dim iRow As Integer
      
    iRow = 3
    Set wksTOC = Sheets("TABLE OF CONTENTS")
    wksTOC.Range("B3:D1000").ClearContents
    
    For Each wks In ThisWorkbook.Worksheets
        
        If wks.Name <> "TABLE OF CONTENTS" Then
            
            Set rng = wks.Cells.Find("PRESENTATION", , xlFormulas, xlWhole)
            
            If Not rng Is Nothing Then
                
                strAdr = rng.Worksheet.Name & "-" & rng.Address
                
                With wksTOC
                    .Cells(iRow, 2) = rng.Text
                    .Cells(iRow, 3) = rng.Worksheet.Name
                    .Cells(iRow, 4) = rng.Address
                    .Hyperlinks.Add Anchor:=.Cells(iRow, 2), _
                                   Address:="", _
                                SubAddress:="'" & rng.Worksheet.Name & "'!" & rng.Address, _
                             TextToDisplay:=rng.Value
                End With
                
                iRow = iRow + 1
                
                Call Other_Finding_InWorksheet(rng, wks, iRow)
            
            End If
        End If
    Next
fpc:
    Set rng = Nothing
    Set wks = Nothing
    Set wksTOC = Nothing

End Sub
Sub Other_Finding_InWorksheet(rng As Range, wks As Worksheet, iRow As Integer)
    
    Dim rngA As Range
    
    Set rngA = wks.Cells.Find("PRESENTATION", rng, xlFormulas, xlWhole)
    
    If rngA Is Nothing Then
        GoTo fpc
    Else
        If rngA.Worksheet.Name & "-" & rngA.Address = strAdr Then
            GoTo fpc
        Else
            With wksTOC
                .Cells(iRow, 2) = rngA.Text
                .Cells(iRow, 3) = rngA.Worksheet.Name
                .Cells(iRow, 4) = rngA.Address
                
                .Hyperlinks.Add Anchor:=.Cells(iRow, 2), _
                               Address:="", _
                            SubAddress:="'" & rng.Worksheet.Name & "'!" & rngA.Address, _
                         TextToDisplay:=rngA.Value
            
            End With
            
            iRow = iRow + 1
            
            Call Other_Finding_InWorksheet(rngA, wks, iRow)
        
        End If
    End If

fpc:
    Set rngA = Nothing

End Sub

ok, so thats a bit of a help. Now I know how to hyperlink text, but that not totaly solves my problem.

I will right my situation exatly:

Well sheet1 has tables which rows are hidden by clicking on checkbox then only visible rows in a table are copied to another sheet ("sheet2"). Each table has its unique name (text that does not repeat in no other table - "presentation", "estimate" and so on) and each time when copied the components in a table differ so the possision of a text also differ. I whant to make a table of contents in begining of "sheet2" that woul only find the names of tables in sheet2 and show the page number (sheet2 has header with page numbers) where the table is, because "sheet2" will be printed and people who will use it must have table of contents so they can navigate through the printed material easily.

So I need table of contents be able to find a page number, but not a sheet name

I want the same that Word does - make a table of contents of all headings, but in excel.