List All Sheet Names

What it does

Big workbooks turn into navigation puzzles. Twenty tabs and you're scrolling the tab bar with the little arrow controls, squinting to find "Q3 Forecast Region 4" before your meeting starts. The macro inserts a fresh "Sheet Index" worksheet at position 1 of the active workbook and fills it with a numbered list of every other sheet, where each sheet name is a hyperlink that jumps to that sheet's A1. A third column shows whether the sheet is currently visible or hidden. Open the workbook, click a name, you're there.

Real-world example

Picture an FP&A manager at a 30-store retail chain handing over a budget workbook to a colleague who's covering for him during paternity leave. The workbook has 47 tabs: one per store for current-year actuals, one per store for next-year budget, plus a few summary tabs. He built it slowly over four years, knows every tab by feel, and never thought about navigation because his fingers know which arrow to scroll past which name.

His colleague does not have that muscle memory. Day one of the handover she opens the file and asks where the consolidated view lives. He points her at "Summary - All Stores", which is tab 23 of 47. She clicks the tab arrow forward eleven times before she finds it. By Friday she's emailed him three times asking where specific tabs are, and he's worried she'll give up and rebuild the model from scratch, which would set her back three weeks.

He runs this macro on the workbook, which inserts a "Sheet Index" tab at position 1 with all 47 tab names as clickable links plus a hidden/visible flag. He saves the file with the index pinned at the front. On Monday his colleague opens it, sees the index, clicks "Summary - All Stores", lands on the right tab, and gets her work done without the email-and-wait loop.

On a 47-tab handover file, the macro saves an unknowable number of "where is this tab" emails, but the realistic estimate of analyst time saved is about 30 minutes across the first week and a much bigger win in collaboration friction throughout the cover period.

How to use

First time? See How to run our tools for the 30-second setup.

1. Open the workbook you want to index. The macro indexes the active workbook.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick ListAllSheetNames, click Run.
3. The macro creates a new sheet called "Sheet Index" at position 1 with hyperlinks to every other sheet. If a sheet called "Sheet Index" already exists, the new one is named "Sheet Index 2" (and so on) so nothing gets overwritten.

FAQ

Does it list hidden sheets too?

Yes. Hidden sheets show up in the index with "hidden" in column C. Clicking the hyperlink jumps to A1 of the hidden sheet but does not unhide it; you'd briefly see the cell in the formula bar without the sheet becoming visible. To navigate to a hidden sheet for editing, right-click the visible tabs and choose Unhide first. The visibility flag in column C exists so you can see at a glance how many sheets are hidden.

Will it overwrite an existing index sheet?

No. If a sheet named "Sheet Index" already exists, the macro creates "Sheet Index 2" instead. If that exists too, it goes to "Sheet Index 3", and so on. So you can run the macro multiple times to refresh the index after adding new tabs without losing the old one. Delete the older copies manually when you're done.

What about Very Hidden sheets?

The xlSheetVeryHidden state shows up as "hidden" in column C, same as regular hidden sheets. The hyperlink still works the same way. The distinction between xlSheetHidden and xlSheetVeryHidden matters mostly for whether right-click > Unhide works (it doesn't for Very Hidden), so the index doesn't bother distinguishing.

Can I refresh the index after adding new tabs?

Yes, but you have to do it manually. Run the macro again to generate a fresh "Sheet Index 2" tab, then delete the older "Sheet Index". A future version may add an in-place refresh that detects an existing index and updates it. For now, regenerate by running the macro a second time.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running undoes the new sheet creation. Because the macro only adds a new sheet (it never modifies existing ones), undo cleanly removes the index tab. The macro runs entirely offline. Free, hosted on GitHub, source visible.