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. 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: Follow the steps below to place this code in the workbook: 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. Download the File from here Note: 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?
It would be nice, if this macro is made to run, every time, if anyone renames the sheet. 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 Cheers
