List And Link All Sheets Excel VBA Macro

It may be useful sometimes to have a list of contents in the spreadsheet as we often do in word documents. This macro example lists all sheets in a workbook and adds a hyperlink to quickly access each of them.

Macro/VBA code:

  Sub ListAndLinkAllSheets()

      Dim ws As Worksheet, r As Integer

      For Each ws In Worksheets

          r = r + 1

 Sheets("Contents").Activate         

 Range("A" & r).Select

          Selection.Value = ws.Name

          Selection.Hyperlinks.Add Anchor:=Selection, Address:="", _

          SubAddress:=ws.Name & "!A1"

      Next

  End Sub

Macro explained:

• First we declare a Worksheet object variable (ws) and an Integer variable (r).
• Then we loop through each individual Worksheet object (ws) in the Worksheets collection of the active workbook with a For Each loop. The variable r holds the row number to list each sheet name.
• We activate the “Contents” sheet as it is there where we want to list all sheet names in the workbook.
• For each sheet we select another cell in column A and add the name of the sheet with the Value property of the Range (Selection is a Range object too) and the Name property of the Worksheet object.
• At the same time, we add a hyperlink (Selection.Hyperlinks.Add) to the same selection (Anchor:=Selection). The Selection refers to the Range object selected before: Range(“A” & r). And then we use the SubAddress attribute to specify the linked location. Note that we use SubAddress when is a place in the same file, but we still need to put the mandatory Address attribute, in this case empty. The Address attribute is used for external links such as a website for example.

This is how we list and link all sheets in Excel with VBA macros.

Other examples:

• How to List Unique Entries with Excel VBA macros
• How to Sort Sheets Alphabetically with Excel VBA macros
• How to List Defined Names with Excel VBA macros