This video will outline an approach to create a table of contents of visible worksheets
in an active workbook, via a customised macro.
As shown in an earlier video,
it can be a challenge to manage multiple worksheets of financial
or general information in a workbook.
A macro with associated hyperlinks to each visible worksheet
will enable users to better navigate around a detailed workbook.
Let’s name this macro listSheetNamesHyperlinks
Ensure to disable the three following Excel properties to improve the macro's performance.
Remember to declare variable names and each type before running the macro.
Like the earlier video,
delete any legacy list of sheet names, because we want only the current list of worksheets.
We can now produce the list of hyperlinks to each visible worksheet
in an active workbook.
The line of code, If ActiveSheet.Name <> wks.Name,
simply means we don’t want to include the current index sheet in our list of worksheets.
Take care with coding this part of the macro,
because it was must be 100% accurate,
otherwise the macro may not work.
The item A1 means the hyperlink will point to this cell address in each visible worksheet.
This line will stipulate the cursor moves to cell address A1,
once the macro has finished the task.
Given our macro is complete,
let’s generate an index list of visible worksheets,
which hyperlink to each worksheet in our active workbook.