Simple VBA Code to create a List of All Sheet Names - With 2 ways Navigation

In this tutorial I show you how to create a list of All sheet names in any workbook (including the Hidden Sheets) by using a simple VBA code. I walk you step by step for creating that code. Then we create a Forward and Backward Navigation from the Index of Sheets to any sheet and back, using a Hyperlink function.

You can Download the exercise file and follow along by clicking on the link:

If you still have difficulty in creating the code, then you can unhide the “Code” worksheet and copy the code and paste it in your visual basic editor.

There is a QUIZ question for you to answer in a comment at 11:18 min.
I also included in the hidden sheet a list of useful VBA Shortcuts, that you can print for future reference.

There are two other methods for creating a List of Sheet Names, either by using Functions or by using Power Query. I sincerely encourage you to watch these 2 other methods and write in a comment, which of the 3 methods you like most. Here are the links to the other methods:

Tutorial for Using Functions

Tutorial for Using Power Query

If you find value in this tutorial give it a thumb up, and hit the “Subscribe” button to be notified when new videos are posted.

[MaayJ]

Never came across a better tutorial on listing 'Sheet Name'. Thanks a ton!

[ltumanyan]

Great video with super clear explanations, thank you very much!!!!

[gopalakrishnarao]

Excellent. You are really a genius. Very useful technique. Time saving Technique. please post this type of time saving tool for the benefit of the Excel you VERY MUCH.

[LotfyKozman]

This is really great. sometimes when yo have more than 20 sheets while preparing a Business Plan you need to make this kind of links.
I'm eager to see how easy is the PQ will handle such unlimited sheet names.
Thanks for the congratulations efforts.

[ozGold]

Very well produced tutorial. Thank-you for the clear instructions and screen tips as you demo. What software(s) are you using?

[kirandeepchoudhry]

Nice way of explaining to write a code from scratch for beginner like me.Instructer what if we want these names to work like hyperlink.

[armandsmit]

Fantastic. Thanks. Now how do I extract/summarise data from the sheets onto the Index page also?

[cherinehaddad]

Thank you so much this was great!!! I do need to add sheets on a weekly basis, but, the VBA is not updating the list. And would it be possible to add a code to always alphabetize the list.

[armotxa]

Great educational lesson.
Thank you.
I have a request.

Sheet A "Sheet is visible, the rest not, when selecting cell D5 "Cell D5 has its own sheets such as sheet a, b, c, d, e"
.
Sheets a, b, c, d, e will be shown again, but the rest will not..

Intention organizes the sheets
.

Thank you in advance.

[huongmuahe]

Can I add a new worksheet to the workbook that has been coded for extracting the list of sheet names?

[ramzanhabib]

Sir Nabeel Murad you Great .it is fabulous but in my worksheets there are charts in that case it gives error while navigating in chart sheet what should i do?

[vitarathiel]

assalamualaikum nabeel morad ...
how to create list of sheets (horizontally & vertically) that can be automatically update/change when i rename/delete/add worksheets.

thankyou

[rikradogt]

É possível colocar a listar por linha e não por coluna?

[KhalilYasser]

Thanks a lot Nabil for these awesome videos.
Can you speak Arabic ? Your name is Arabic name .. Are you from Arabian country?


As for th quiz: Ctrl + Shift + L is not VBA shortcut (It if for Filter from Data tab)

[mailashish]

Why did u write on error goto 0

And how did u refer the sheet by writing worksheets(1)

I mean if i open the workbook and i in sheet say 'Report' which is the second last sheet then will it add a sheet at the beginning of WB. That the new sheet which will get created will the first sheet.

Can u also make a video on different wats of sheet referencing.

Last thing ur explanation is super easy to understnd. Plz be simple in future too.

[kirandeepchoudhry]

Ctrl+shift+L .It is for filter as I know

[ShaibulHoqueMozumder]

Sub ExtractName()
On Error GoTo ExitHandler
Worksheets.Add Before:=Worksheets(1)
ActiveSheet.Name = "Index"
Dim MyIndex As Worksheet
On Error GoTo 0
With Range("A1")
.Value = "Sheet Name"
.Font.Size = 14
.Font.Bold = True
.Font.Color = vbBlue
End With
ActiveCell.Offset(1, 0).Select
For Each MyIndex In Worksheets
If MyIndex.Name <> "Index" Then
ActiveCell.Value = MyIndex.Name
ActiveCell.Offset(1, 0).Select
End If
Next MyIndex
Columns("A").AutoFit
Exit Sub
ExitHandler:
MsgBox "You Already have a List of Sheets"
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Exit Sub
End Sub