Simple VBA Code to create a List of All Sheet Names - With 2 ways Navigation
HTML-код
- Опубликовано: 21 окт 2024
- 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:
www.amazon.ca/...
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
• Extract a List of Work...
Tutorial for Using Power Query
• Create a List of Sheet...
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.
Never came across a better tutorial on listing 'Sheet Name'. Thanks a ton!
This is the best VBA tutorial!
Thank you
Great video with super clear explanations, thank you very much!!!!
You're very welcome!
Thank you! Excellent tutorial😀
Fantastic. Thanks. Now how do I extract/summarise data from the sheets onto the Index page also?
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 learners/users/addicts/lovers.Thank you VERY MUCH.
Thank you Gopala... I don't know how I missed this nice comments for a long time
Amazing video thank u Nabil :)
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.
Be ready my friend... The Power Query solution has been posted and will be public tomorrow (Friday 12 April)...
Your comments and encouragement mean a lot to me.... I still remember your request and you'll see VERY soon lots of tutorials on Dynamic Arrays.
By the way, read the comment that Yasser Khalil wrote above (as well as my reply) and let me know your opinion: Would it be a good or bad idea to post some tutorials in Arabic using English interface and English Excel terminology while speaking in Arabic.?? are you with or against??
Very well produced tutorial. Thank-you for the clear instructions and screen tips as you demo. What software(s) are you using?
I'm using Excel 2016 for this tutorial
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.
Thanks a million.👍👍👍👍👍
Excellent tutorial
Glad you liked it
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.
Thank you Kirandeep... I explained how to create the Hyperlink in the other 2 parts (Functions & Power Query) . Although it could be done also in VBA but I did not want to complicate the video session for less advanced users
@@Officeinstructor We really want to know how to add hyperlinks with this code. All the other VBA for index use a different technique that I can't seem to apply to your code. Please could you let us know the lines to replace inside your IF statement? We would really appreciate this :)
@@turbochimps Due to the huge load of comments and requests on my different social platforms... I am unable to answer individual requests. Thank you for your understanding.
@@Officeinstructor yet the number of letters used to write your response is more than what is required to write the actual code 😅
Excellent Vdo ...
Many many thanks
Can I add a new worksheet to the workbook that has been coded for extracting the list of sheet names?
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?
Thanks Ramzan...
Each work situation needs a customization of the code.
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.
I will have to check myself
assalamualaikum nabeel morad ...
how to create list of sheets (horizontally & vertically) that can be automatically update/change when i rename/delete/add worksheets.
thankyou
É possível colocar a listar por linha e não por coluna?
good 👍🏻👍🏻👍🏻👍🏻
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.
On Error GoTo 0
This is proceed with the instructions if there is No error
Sheets can be called in different ways, among them "Index Numbers" the leftmost is number one
@@Officeinstructor i am afraid i m still not able to grab it fully.
I mean if index worksheet is already there then it will go to exit handler and it will delete the newly created sheet and it will exit sub and code will end.
Even if i dnt write this that is on error go to 0 then lets say there is no index sheet then also code will go smoothly. Isn'it??
One more can u make a video on sheet referencing....
I m basically new to VBA n i m struggling to do referencing of sheets. My request to u....plz make one.
thanks
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)
i'm really happy to have you as one of my loyal fans who always give me a feedback and keep me motivated.
And YES, I am Canadian -Egyptian. Of course I speak Arabic but I do not deliver Excel courses in Arabic for some reasons:
1- Although speaking Arabic is not a problem (It's my first language though) However, I do not know the Excel terminology in Arabic (or most of it).
2- I do not have an Arabic Excel interface or even an Arabic keyboard.
3- I feel like the terms are not close to my mind as in English say Conditional Formatting (El Tanseek el Sharty)
4- I am not quite sure if I Talk in Arabic but use English Excel Terminology, how will that be received. In Egypt they will call it (Tanaka)
at the end,I wanna say, I would love to share my knowledge with Arabic speaking fans but really need to open a discussion on that...
@@Officeinstructor Thanks a lot Nabil for your reply and you are awesome person (No Tanaka in my opinion :) ) ... And it will be out pleasure if you share your videos on our forum at this link and you will indeed find expert people (may be not too many people but there are some great experts too.
excel-egy.com/forum/
Ctrl+shift+L .It is for filter as I know
You are right Kirandeep... better use a different shortcut CTRL+SHIFT+K (K is for Kirandeep)
@@Officeinstructor Are you kidding. is this key for filter.Thanks for giving new trick
@@kirandeepchoudhry9232 CTRL + SHIFT + L is the default Shortcut for Filter. What I mentioned is a custom shortcut for he code
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
😂😂😂