Hello this video will show one way to compose a macro, which will not only list visible worksheets,
but also hidden worksheets in a hyperlinked table of contents.
As per earlier videos, it can sometimes be quite challenging navigating through numerous
worksheets containing different financial information.
Spreadsheet users often overlook or forget about the potential for hidden worksheets too.
This macro generated approach will help to improve this issue,
and allow users to navigate to separate sheets;
including hidden ones which will be individually denoted.
I'm going to call this macro listAllSheetNamesHyperlinks
Disable these Excel properties.
Declare variable names.
Delete any existing list of worksheet names.
We want to commence the list on
cell address A2.
We can now write the code to generate the list of worksheets with their hyperlink.
The sequencing of the logic is crucial and must be accurate,
otherwise the macro won't execute properly.
Remember this first part of the IF statement will capture visible worksheets only
This Offset line will move the cursor down to the next row in the list.
The Else part will capture all hidden worksheets for the worksheet list.
The following code will denote if the worksheet is hidden.
This will move the cursor to cell A1
and enter the title for our list of worksheets.
Now the macro is complete,
we can test it to ensure we are listing all worksheets
- both visible and hidden.
Remember although we have a hyperlink
for a hidden sheet like Delta,
the link will not allow us to go to the hidden worksheet.
It is here for informational purposes only.