Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Index with Table of Contents and Hyperlinks

0

Hello Excel Wizards

I'm trying to produce an index page for my Excel Workbook, with a hyperlink for each sheet, along with several cells (the same cell refernce on each sheet) also copied to the Index page to form a detailed Table of Contents.

I have the following to provide me with a list of Hyperlinked sheet names, but can't work out how to add the other cells to form a table:

Sub GenIndexSheet()
    Dim Ws As Worksheet
    Dim i As Long: i = 1
        
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    'Create an Index Sheet. If already existing, clear it.
    On Error Resume Next
    Set Ws = Worksheets("Index")
    If Err.Number = 0 Then
        Worksheets("Index").ClearContents
        Else
        On Error GoTo 0
        Worksheets.Add(Before:=Worksheets(1)).Name = "Index"
    End If
    
    Worksheets("Index").Activate
    Range("A1") = "Index"
    Range("A1").Font.Bold = True
    Range("A1").Font.Size = 20
    
    For Each Ws In Worksheets
        If Ws.Name <> "Index" Then
            i = i + 1
            Ws.Hyperlinks.Add Anchor:=Cells(i, 1), Address:="", SubAddress:="'" & Ws.Name & "'!A1", TextToDisplay:=Ws.Name
        End If
    Next Ws
    
    Worksheets("Index").Columns(1).AutoFit
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub



I'd like the table to look something like this:

Index

Sheet 1 Cell B1 Cell A2

Sheet 2 Cell B1 Cell A2

Sheet 3 Cell B1 Cell A2


Many thanks in advance

Answer
Discuss

Answers

2
Selected Answer

Jon

Given you have the Index worksheet activated, you can  add the values from B1 and A2 of the other sheets by adding the lines in bold below:

Sub GenIndexSheet()
    Dim Ws As Worksheet
    Dim i As Long: i = 1

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    'Create an Index Sheet. If already existing, clear it.
    On Error Resume Next
    Set Ws = Worksheets("Index")
    If Err.Number = 0 Then
        Worksheets("Index").ClearContents
        Else
        On Error GoTo 0
        Worksheets.Add(Before:=Worksheets(1)).Name = "Index"
    End If

    Worksheets("Index").Activate
    Range("A1") = "Index"
    Range("A1").Font.Bold = True
    Range("A1").Font.Size = 20

    For Each Ws In Worksheets
        If Ws.Name <> "Index" Then
            i = i + 1
            Ws.Hyperlinks.Add Anchor:=Cells(i, 1), Address:="", SubAddress:="'" & Ws.Name & "'!A1", TextToDisplay:=Ws.Name
            Cells(i, 2).Value = Ws.Range("B1").Value
            Cells(i, 3).Value = Ws.Range("A2").Value
        End If
    Next Ws

    Worksheets("Index").Columns(1).AutoFit

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Hope this helps.

Discuss

Discussion

Thanks John, I knew it would be simple :)
JonP (rep: 37) Jul 4, '22 at 11:44 am
Thanks for selecting my Answer Jon.

Forgot to say that you might want to change your autofit line to:
Worksheets("Index").Columns("A:C").AutoFit
John_Ru (rep: 3992) Jul 4, '22 at 11:50 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login