Quickly Create Summary Worksheet with Hyperlinks in Excel

A lot of my colleagues spend a lot of their time in creating a Summary Worksheet in Excel.

A typical summary worksheet has the names of all the worksheets in different cells and all the names also hyperlinked to these worksheets.

So you can click on a cell with a sheet name (say Jan, Feb, Mar…) and it will take you to that worksheet. Additionally, there is also a hyperlink on each worksheet that links back to the summary worksheet.

While my colleagues have become super efficient in doing this, it’s still a waste of time when you can do the same thing in less than a second (yes you read it right).

The trick is to create a short macro that will do it for you.

No matter how many worksheets you have, it will instantly create a summary worksheet with working hyperlinks.

Something as shown below:

Create Summary Worksheet with Hyperlinks in Excel - Demo

As you can see in the image above, it instantly creates the summary when you run the macro (by clicking on the button). The sheet names are hyperlinked which takes you to the worksheet when you click on it.

Create Summary Worksheet with Hyperlinks

All the heavy lifting in creating the summary worksheet is done by a short VBA code. You just need to run the code and take a break as you would have some free time now 🙂

Here is the code:

Sub CreateSummary()
'Created by Sumit Bansal of TrumpExcel.com
'This code can be used to create summary worksheet with hyperlinks
Dim x As Worksheet
Dim Counter As Integer
Counter = 0
For Each x In Worksheets
Counter = Counter + 1
If Counter = 1 Then GoTo Donothing
    With ActiveCell
        .Value = x.Name
        .Hyperlinks.Add ActiveCell, "", x.Name & "!A1", TextToDisplay:=x.Name, ScreenTip:="Click here to go to the Worksheet"
        With Worksheets(Counter)
            .Range("A1").Value = "Back to " & ActiveSheet.Name
            .Hyperlinks.Add Sheets(x.Name).Range("A1"), "", _
            "'" & ActiveSheet.Name & "'" & "!" & ActiveCell.Address, _
            ScreenTip:="Return to " & ActiveSheet.Name
        End With
    End With
ActiveCell.Offset(1, 0).Select
Donothing:
Next x
End Sub
Where to Put this Code?

Follow the steps below to place this code in the workbook:

  1. Go to Developer Tab and Click on Visual Basic. You can also use the keyboard shortcut – ALT F11.
    • If you can find the developer tab in the ribbon in Excel, click here to know how to get it.Create Summary Worksheet with Hyperlinks in Excel - VB in Developer Tab
  2. There should be a Project Explorer pane at the left (if it is not there, use Control + R to make it visible).Create Summary Worksheet with Hyperlinks in Excel - Project Explorer
  3. Go to Insert and Click in Module. This adds a module to the workbook. Also, in the right, you would see the code window appears (with a blinking cursor). Create Summary Worksheet with Hyperlinks in Excel - insert module
  4. In the module code window, copy and paste the above code.Create Summary Worksheet with Hyperlinks in Excel - code
Running the Code

To run this code:

  • Go to Developer Tab –> Code –> Macros. This will open the Macro Dialogue box.Create Summary Worksheet with Hyperlinks in Excel - Macros
  • Select the Macro CreateSummary and click on Run.Create Summary Worksheet with Hyperlinks in Excel - Run Macro
  • This will run the macro and create the hyperlinks in the active sheet.

Another way to run the macro is to create a button and assign the macro to it. To do this:

  • Insert a shape in the worksheet. Format the shape the way you want. Create Summary Worksheet with Hyperlinks in Excel - insert shape
  • Right-click on it and select Assign Macro.Create Summary Worksheet with Hyperlinks in Excel - Assign Macro
  • In the Assign Macro box, select the macro you want to assign to the shape and Click OK.Create Summary Worksheet with Hyperlinks in Excel - Assign Macro box

Now, you can simply click on the shape to run the macro.

Download the File from here
Download File

Note:

  1. I have hard-coded the cell A1 in each sheet, which is hyperlinked to get you back to the summary sheet. Ensure that you change it accordingly if you have something already in A1 cell in each sheet.
  2. The summary does not create a hyperlink for itself (which makes sense as you are already on that sheet).
  3. Run this code when the Summary Worksheet is the active worksheet.
  4. You may want to add some formatting or rearrangement. But I hope this code takes care of the hard part.
  5. Save this workbook as .xls or .xlsm extension, as it contains a macro.

Other Excel VBA tutorials:

  • Terry says:

    The code doesn’t work if the sheet names have spaces. Had to add the single quotes round the sheet names, then it works.
    Thanks

  • Erica Dyson says:

    Hi there.. how can I make it work when there are spaces in the sheet name?

  • kumarapush says:

    I was thinking, what happens if the user renames a sheet?

    It would be nice, if this macro is made to run, every time, if anyone renames the sheet.

  • admin says:

    Thanks for pointing out the glitch Manda. Have corrected the code and updated the download file. Hope this works fine now.

    Feel free to refer to this article in your blog 🙂

  • Manda Imms says:

    Hi

    The macro works brilliantly – kudos. 🙂

    Should let you know – the “Back to Summary Tab” hyperlinks don’t actually link back to the Summary sheet, just to A1 on their own sheet, causing a “Reference not valid” error message to appear when you click on them.

    Okay for me to refer to your work on my blog with a link to your post? excel-pixie.com/wp
    Cheers

  • >