If you have an Excel spreadsheet with several worksheets, you might want to have a single worksheet at the beginning that has links to all the worksheets.
Add the Developer Ribbon
You must use the Developer tools to create the macro. Use the following procedure if you do not have access to this ribbon:
- In Excel, go to File > Options.
- Click Customize Ribbon.
- In the Customize the Ribbon list, select Developer.
Create the macro
You must create a macro. After you create the macro, you can use it for all your Excel workbooks.
- Go to Developer and click Visual Basic.
- In the Project – VBAProject pane at the top left, right-click the name of your workbook. Then, select Insert > Module.
- In the panel that is shown on the right, paste the following code:
Sub CreateLinksToAllSheets() Dim sh As Worksheet Dim cell As Range For Each sh In ActiveWorkbook.Worksheets If ActiveSheet.Name <> sh.Name Then ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ "'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name ActiveCell.Offset(1, 0).Select End If Next sh Next sh End Sub
- Click Save and close the window.
- If you want to add a keyboard shortcut, go to Developer and click Macros.
- Select the macro, and click Options… You can enter a letter or number to use with the Ctrl key as a shortcut.
Run the macro
- To run the macro, go to the sheet where you want the list created.
- If you start on the first worksheet, the macro will create a list starting from the second worksheet to the end. Note: If you start on the second worksheet, the macro will create a list with a link to the first worksheet, and then the third worksheet to the end. It does not include a link for the current worksheet.
- Place your cursor in the cell where you want the list to begin.
- Go to the Developer tab and click Macros.
- Select the name of the macro and click Run.
The following is an example of the results.