Automatically create a list of Excel worksheet links

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:

  1. In Excel, go to File > Options.
  2. Click Customize Ribbon.
  3. In the Customize the Ribbon list, select Developer.
dev ribbon

Create the macro

You must create a macro. After you create the macro, you can use it for all your Excel workbooks.

  1. Go to Developer and click Visual Basic.
  2. In the Project – VBAProject pane at the top left, right-click the name of your workbook. Then, select Insert > Module.
    vba
  3. 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(10).Select
         End If
     Next sh
    Next sh
     End Sub
  4. Click Save and close the window.
  5. If you want to add a keyboard shortcut, go to Developer and click Macros.
  6. 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

  1. To run the macro, go to the sheet where you want the list created.
  2. 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.
  3. Place your cursor in the cell where you want the list to begin.
  4. Go to the Developer tab and click Macros.
  5. Select the name of the macro and click Run.

The following is an example of the results.

results

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s