Goal: To create a table of contents of hyperlinks to selected sheets using Excel VBA

Hi there, this feature is a follow-on to an earlier video on writing a macro to create hyperlinks to worksheets in Excel. As we can see, we have our list of worksheets containing financials for different business units.

However, what if we want to create a table of contents based on selected worksheets only?

The macro

Go to Visual Basic for Applications and insert a module. It is best to insert the Option Explicit syntax at the start of your module, because it will force us to implicitly declare variables for each individual macro we write.

We will call the macro, listSelectedSheetNames

Next we want to disable these Excel properties, in order to improve the performance of the macro. Now we need to declare our object variables for the selected sheets hyperlinks macro. Then we must set the values for our two object variables. First, we need to state the startWks or our start worksheet. Then out start range or startCell in our index sheet.

We are going to add an error handler to the macro, which will prevent the macro running if only our index sheet is selected. Otherwise the macro will proceed to clear all cells in columns A to B in index sheet (Sheet(1) or startWks).

The above line of highlighted code is critical, in order to reference solely the selected worksheets in the active workbook. Whilst the last line of code, If startWks.Name <> wks.Name Then, will prevent the macro referencing the index page (Sheet(1)). We can now write the code to reference the selected worksheets.

The above portion of the code is the most critical, because we are dynamically referencing each separate worksheet in our selected sheets. Please take your time when writing this part of the code, as a basic misspelling will void the macro’s complete and successful creation of the hyperlinked table of contents.

The Next wks syntax will move the macro to the next worksheet in the selection.

Given the macro has looped through all of the worksheets, we are ready to move the cursor back to the index sheet. Let’s label cell A1 in the index sheet, “Table of Contents” with a bold font.

Once the macro is complete, it is best practice to clear the two object variables, so the macro can run again from scratch without remembering these variables going forward.

We can now re-enable the Excel properties, which were earlier disabled because our macro is complete.

We have finished writing the macro; let’s review our macro one final time.

Running the macro

Let’s run our macro. We will choose the sheets, Alpha, Juliett, Uniform and Whisky

As we can see our macro is working, and is correctly hyperlinking to the respective worksheets.

Here is the example workbook, which contains the listSelectedSheetNames macro.

Download SpreadsheetVault – Create index with hyperlinks to all sheets using Excel VBA (solution).xlsb