Hello Excellers and welcome back to another #MacroMonday #Excel tip in my 2019 Series. I want to share with you a handy Excel macro that will create a hyperlink to all of the worksheets in your workbook. So you can quickly create an Index page which will display hyperlinks to all other worksheets in your workbook. I use hyperlinks to different parts of my Excel workbook solutions regularly, and to have a quick way to create them is awesome.

Where To Store The Macro.

First, we need to decide where to store our macro. We then have a choice. To store your code either in your Personal Macro Workbook or in your current workbook.  What’s the difference?.  Well, if you save the macro in your Personal Macro workbook it will be available in any Excel workbooks.  If you store it in the current workbook then use is restricted to that workbook. In this instance, I may want to reuse the code so I will store it in my Personal Macro Workbook.

Learn More About Your Personal Macro Workbook (PMW).

Macro Mondays -Creating and Updating Your Personal Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook

Starting The Macro.

We need to start off the macro by inserting a New Module.  Do this by selecting the Personal.xlsb workbook, then Insert Module.  Type Sub then the name of your macro.  In this example, I have called it CreateLinksIndex. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine.  We simply need to enter the rest of the code between these two lines.

[stextbox id=’info’]
Sub CreateLinksIndex()
End Sub
[/stextbox]

Declaring Variables.

We need to declare any variables.  This ensures that Excel creates a memory container for these values  In this example, we need to declare the following.

[stextbox id=’info’]
Dim sh As Worksheet
Dim cell As Range
[/stextbox]

sh is declared as a Worksheet Object, and cell declared a Range Object. A range object can contain a single cell or multiple cells.

Use The For Next Loop With IF Then.

The next stage is to use For…Next looping method. Each worksheet in active workbook is stored in sh, one by one. Excel avoids lining to the current active worksheet with the If …Then code.

[stextbox id=’info’]
For Each sh In ActiveWorkbook.Worksheets
If ActiveSheet.Name <> sh.Name Then
[/stextbox]

Create The Hyperlinks To Worksheets.

Excel creates a Hyperlink to the worksheet stored in the object sh. The link name is taken from the worksheet name. It is placed in the cell that is selected in the active worksheet. Each of the worksheet names is then offset by one row creating a list of worksheet hyperlinks as each of the worksheets stored in the object sh loop.

[stextbox id=’info’]
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:=””, SubAddress:= _
“‘” & sh.Name & “‘” & “!A1”, TextToDisplay:=sh.Name
ActiveCell.Offset(1, 0).Select
[/stextbox]

Work Through All Of The sh Objects.

Excel loops through all of the “For each” statement and store next worksheet in sh worksheet object.

[stextbox id=’info’]
End If
Next sh
[/stextbox]

Ending The Excel Macro.

Once all of the worksheets have hyperlinks the code then ends. The End Sub piece of code already is written as is generated automatically when first starting the macro in Step 1.

[stextbox id=’info’]
End Sub
[/stextbox]

That’s how easy it is to create an Index page of hyperlinks to all of your Excel worksheets. I use this all of the time. How do you create your hyperlinks?.

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below

 

How To Excel At Excel – Macro Mondays Blog Posts.

 

So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.