How can I create one hyperlink to each worksheet in one index sheet?

Question

Edit: After doing a bit more research I stumbled on this handy little shortcut: Just right click on the little arrows on the bottom left corner to show all sheets - no code required!

---

I have an excel workbook with 100 tabs. Luckily for me the tabs are all numbered 1-100. I Have an index page with all the numbers in a row and I would like to make a row next to that row with a hyperlink to the numbered tab.

   A        B
---------------------------
|  1   | link to tab 1    |
---------------------------
|  2   | link to tab 2    |
---------------------------

etc...

So far the most promising thing I've found is:

=Hyperlink(“C:\Documents and Settings\Admin1\Desktop\” & A1 & “.xls”,A1)

I know that the hyperlink function expects:

=HYPERLINK(link_location,friendly_name)

And when I do it manually, I get this:

=HYPERLINK('1'!$A$1,A1)

So I want to do something like this:

=HYPERLINK('& A1 &'!$A$1,A1)   

But it's not working. Any help is much appreciated. Also, if there is an easier way to approach this - I am all ears.

Solution 1

With code something like this

1. Press Alt + F11 to open the Visual Basic Editor (VBE).
2. From the Menu, choose Insert-Module.
3. Paste the code into the right-hand code window.
4. Close the VBE, save the file if desired.

In excel-2003 go to Tools-Macro-Macros and double-click CreateTOC
In excel-2007 click the Macros button in the Code group of the Developer tab, then click CreateTOC in the list box.

Option Explicit

Sub CreateTOC()
    Dim ws As Worksheet
    Dim nmToc As Name
    Dim rng1 As Range
    Dim lngProceed As Boolean
    Dim bNonWkSht As Boolean
    Dim lngSht As Long
    Dim lngShtNum As Long
    Dim strWScode As String
    Dim vbCodeMod

    'Test for an ActiveWorkbook to summarise
    If ActiveWorkbook Is Nothing Then
        MsgBox "You must have a workbook open first!", vbInformation, "No Open Book"
        Exit Sub
    End If

    'Turn off updates, alerts and events
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With

    'If the Table of Contents exists (using a marker range name "TOC_Index") prompt the user whether to proceed
    On Error Resume Next
    Set nmToc = ActiveWorkbook.Names("TOC_Index")
    If Not nmToc Is Nothing Then
        lngProceed = MsgBox("Index exists!" & vbCrLf & "Do you want to overwrite it?", vbYesNo + vbCritical, "Warning")
        If lngProceed = vbYes Then
            Exit Sub
        Else
            ActiveWorkbook.Sheets(Range("TOC_Index").Parent.Name).Delete
        End If
    End If
    Set ws = ActiveWorkbook.Sheets.Add
    ws.Move before:=Sheets(1)
    'Add the marker range name
    ActiveWorkbook.Names.Add "TOC_INDEX", ws.[a1]
    ws.Name = "TOC_Index"
    On Error GoTo 0

    On Error GoTo ErrHandler

    For lngSht = 2 To ActiveWorkbook.Sheets.Count
        'set to start at A6 of TOC sheet
        'Test sheets to determine whether they are normal worksheets
        ws.Cells(lngSht + 4, 2).Value = TypeName(ActiveWorkbook.Sheets(lngSht))
        If TypeName(ActiveWorkbook.Sheets(lngSht)) = "Worksheet" Then
            'Add hyperlinks to normal worksheets
            ws.Hyperlinks.Add Anchor:=ws.Cells(lngSht + 4, 1), Address:="", SubAddress:="'" & ActiveWorkbook.Sheets(lngSht).Name & "'!A1", TextToDisplay:=ActiveWorkbook.Sheets(lngSht).Name
        Else
            'Add name of any non-worksheets
            ws.Cells(lngSht + 4, 1).Value = ActiveWorkbook.Sheets(lngSht).Name
            'Colour these sheets yellow
            ws.Cells(lngSht + 4, 1).Interior.Color = vbYellow
            ws.Cells(lngSht + 4, 2).Font.Italic = True
            bNonWkSht = True
        End If
    Next lngSht

    'Add headers and formatting
    With ws
        With .[a1:a4]
            .Value = Application.Transpose(Array(ActiveWorkbook.Name, "", Format(Now(), "dd-mmm-yy hh:mm"), ActiveWorkbook.Sheets.Count - 1 & " sheets"))
            .Font.Size = 14
            .Cells(1).Font.Bold = True
        End With
        With .[a6].Resize(lngSht - 1, 1)
            .Font.Bold = True
            .Font.ColorIndex = 41
            .Resize(1, 2).EntireColumn.HorizontalAlignment = xlLeft
            .Columns("A:B").EntireColumn.AutoFit
        End With
    End With

    'Add warnings and macro code if there are non WorkSheet types present
    If bNonWkSht Then
        With ws.[A5]
            .Value = "This workbook contains at least one Chart or Dialog Sheet. These sheets will only be activated if macros are enabled (NB: Please doubleclick yellow sheet names to select them)"
            .Font.ColorIndex = 3
            .Font.Italic = True
        End With
        strWScode = "Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)" & vbCrLf _
                    & "     Dim rng1 As Range" & vbCrLf _
                    & "     Set rng1 = Intersect(Target, Range([a6], Cells(Rows.Count, 1).End(xlUp)))" & vbCrLf _
                    & "     If rng1 Is Nothing Then Exit Sub" & vbCrLf _
                    & "     On Error Resume Next" & vbCrLf _
                    & "     If Target.Cells(1).Offset(0, 1) <> ""Worksheet"" Then Sheets(Target.Value).Activate" & vbCrLf _
                    & "     If Err.Number <> 0 Then MsgBox ""Could not select sheet"" & Target.Value" & vbCrLf _
                    & "End Sub" & vbCrLf

        Set vbCodeMod = ActiveWorkbook.VBProject.VBComponents(ws.CodeName)
        vbCodeMod.CodeModule.AddFromString strWScode
    End If

    'tidy up Application settins
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With

ErrHandler:
    If Err.Number <> 0 Then MsgBox Err.Description & vbCrLf & "Please note that your Application settings have been reset", vbCritical, "Code Error!"
End Sub

Solution 2

My snippet:

        Sub AddLinks()
            Dim wksLinks As Worksheet
            Dim wks As Worksheet
            Dim row As Integer
            Set wksLinks = Worksheets("Links")
            wksLinks.UsedRange.Delete
            row = 1
            For Each wks In Worksheets
               ' Debug.Print wks.Name
                wks.Hyperlinks.Add wksLinks.Cells(row, 1), "", wks.Name & "!A1", , wks.Name
                row = row + 1
            Next wks
        End Sub

Assumes a worksheet named 'Links"

Comments

[Siddharth Rout]

+ 1 Simply beautiful :)

[Eitan]

Wow - amazing! Thanks :)

[rahul]

can you add a hyperlink in all the worksheets that refer back to TOCIndex?