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:
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:
- 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.
- There should be a Project Explorer pane at the left (if it is not there, use Control + R to make it visible).
- 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).
- In the module code window, copy and paste the above code.
Running the Code
To run this code:
- Go to Developer Tab –> Code –> Macros. This will open the Macro Dialogue box.
- Select the Macro CreateSummary and click on Run.
- This will run the macro and create the hyperlinks in the active sheet.
Another way to run the macro is to insert a button/shape and assign the macro to it. To do this:
- Insert a shape in the worksheet. Format the shape the way you want.
- Right-click on it and select Assign Macro.
- In the Assign Macro box, select the macro you want to assign to the shape and Click OK.
Now, you can simply click on the shape to run the macro.
- 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.
- The summary does not create a hyperlink for itself (which makes sense as you are already on that sheet).
- Run this code when the Summary Worksheet is the active worksheet.
- You may want to add some formatting or rearrangement. But I hope this code takes care of the hard part.
- Save this workbook as .xls or .xlsm extension, as it contains a macro.
Other Excel VBA tutorials:
- Get Multiple Lookup Values Without Repetition in a Single Cell.
- Task Prioritization Matrix – VBA Application.
- How to Combine Multiple Workbooks into One Excel Workbook.
- Excel VBA Loops – For Next, Do While, Do Until, For Each (with Examples).
- How to Record a Macro in – A Step by Step Guide.
- How to Quickly Remove Hyperlinks from a Worksheet in Excel.
- Online Excel VBA Course.
- How to Switch Between Sheets in Excel?
10 thoughts on “Quickly Create Summary Worksheet with Hyperlinks in Excel”
Wow, that was amazing, what a great makro, thank you for posting
Hi there. Great stuff here! I would like to know if you provide tutorial for extraction of particular cells to be placed in the summary worksheet alongside the worksheet hyperlink that the cell value was extracted from.
Thanks for the macro! I have been researching ways to easily create a summary sheet for use by another company member. The link “return to summary” works, to go back to the summary page, but the hyperlinks do not work. There are spaces in most of the sheet names, which I see from other comments may create the problem. How do I fix that? Can I change something in the macro itself? There are over 300 sheets in this file, so doing name changes for the individual sheets is not an option at this point.
Thank you! excellent steps by step tutorial all worked as expected!:)
The code doesn’t work if the sheet names have spaces. Had to add the single quotes round the sheet names, then it works.
Hi there.. how can I make it work when there are spaces in the sheet name?
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.
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 🙂
Im still getting a reference is not valid error, i have a hyphen in my sheets. Is there a fix for this?
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
Comments are closed.