List All Your Sheets Efficiently in Excel (10 Levels)
HTML-код
- Опубликовано: 10 июл 2024
- Discover how to list all worksheets in Excel. ➡️ Click to show more
Do you need a complete list of all your sheets in Excel? If you have 20, 50, or even 100 sheets, you don't want to go through the whole list by hand.
Learn the methods in this video and you won't have to. Instead, you'll be able to generate your perfect list of all your sheets, error-free, and in record time.
I'll show you how to use a secret formula that almost nobody knows about, and then I'll take it to the next level by showing you how to code a solution with VBA.
You'll create a list of sheet names that you can click on like a table of contents. And add a back button to every sheet that takes you back to the list of sheets.
I've broken down this video into 10 levels so it's easier to follow. And if you want to get straight into the VBA solution, it starts at level 3.
This step-by-step guide is perfect for intermediate Excel users looking to become advanced Excel users.
🅰️ If you prefer to read here is the article with full written instructions:
www.launchexcel.com/list-all-...
🅱️ Download the sample workbook with code:
d1yei2z3i6k35z.cloudfront.net...
🔹 Chapters:
00:00 Introduction
00:57 Level 1: Manual Tricks for Listing Sheets
01:39 Level 2: Use Formulas for Sheet Listing
05:50 Level 3: Automate with VBA
10:51 Level 4: Enhance VBA with New Features
13:54 Level 5: Error Checking and Improvements
16:11 Level 6: Optimize Sheet List Display
18:04 Level 7: Add Padding for Readability
22:07 Level 8: Create Clickable Sheet Names
24:26 Level 9: Add Back Buttons for Easy Navigation
27:03 Level 10: Automate Sheet List Refresh
29:32 Conclusion and Resources
🚀 BEST EXCEL RESOURCES
Launch Excel Macros & VBA School:
Say Goodbye to Tedious Manual Work and Automate Your Way with Excel VBA. Our course helps you to learn Excel VBA and save hours of time every week even if you have zero prior experience with programming.
➡️ www.launchexcel.com/shop
Excel Dashboards: Learn how to make killer dashboards in Excel. They will set your skills apart from the crowd. Invest in your data reporting and presentation skills.
➡️ go.launchexcel.com/moth-dashb...
Explore 100+ Excel Templates: Save time and streamline your workflow. Here is a selection of over 100 spreadsheet templates. They cover Accounting, Finance, Data Analysis, HR, Operations, Project Management, Real Estate, Sales & Marketing, Small Business, and Management.
➡️ www.launchexcel.com/best-exce...
🚀 Ready to launch your Excel skills into orbit?
Subscribe to the Launch Excel channel for more stellar Excel tips and tricks! And check out our website www.launchexcel.com for in-depth tutorials. 
Хобби
*❤ CHAPTERS:*
00:00 - Introduction
00:57 - Level 1: Manual Tricks for Listing Sheets
01:39 - Level 2: Use Formulas for Sheet Listing
05:50 - Level 3: Automate with VBA
10:51 - Level 4: Enhance VBA with New Features
13:54 - Level 5: Error Checking and Improvements
16:11 - Level 6: Optimize Sheet List Display
18:04 - Level 7: Add Padding for Readability
22:07 - Level 8: Create Clickable Sheet Names
24:26 - Level 9: Add Back Buttons for Easy Navigation
27:03 - Level 10: Automate Sheet List Refresh
29:32 - Conclusion and Resources
Thanks a lot !!!
You are welcome!
Welcome back Victor - it's nice to see some new videos from you.
Thanks Mark!
Thanks A lot
You're welcome 😄
Thank you Victor for the excellent video.
Hey Mark, thanks for the comment 😄
Excellent video, especially the boss level part
Thanks for your comment. Glad you like the boss level 👍
Impressive. Thanks for the video
Sure, glad you like it
Hai Victor,,.. excellent video...
if you could just advise on how to insert macro command on how to position the Navigation button
thank you
Sure! To position the back navigation button check out the tutorial at 25:45.
There is a line of code to add the button, and you can position it at the same time.
' Add the button (shape, X, Y, Width, Height)
Set btn = ws.Shapes.AddShape(msoShapeRoundedRectangle, 5, 5, 75, 30)
Change the X and Y from 5, 5 to other values to position the top left corner where you want.
And change the Width and Height from 75, 30 to other values to resize as you like.
Super video Victor. Glad I stumbled onto your channel. I really like your delivery. Now here is the challenge: How do I do the same things in LibreOffice/Calc? Though probably not the vba part.
I'm happy you like my delivery! One of the big strengths of Excel is the ability to automate and enhance with VBA. Most Excel users ignore VBA, but I think it's one of the most useful features in Excel.
Re: your challenge, sadly I've never used LibreOffice so I can't advise you on how to do this. Good luck with looking for a solution.
Great help to learners.. can we make list from another workbook also?
@@planningpedia yes, if you know the name of the other workbook, you can specify the worksheets in that workbook. Use Workbooks (index), where index is the workbook name,. e.g. Workbooks("Name_Of_Other_Workbook.xlsx")
Nice video.
Please keep us informed when the download file is available.
Thank you.
Thanks Rudi! Yes I'll get the download file ready with the article. Links coming soon 😄
This was a great video, however, that VBA is very complicated. I do know excel very well, just not the VBA stuff. I was wondering if there is a way to do the hyperlink back and forth if you just did the transpose part to get the sheet names? I have 193 or so sheets in one of my excel sheets and would like to go back and forth but without all the VBA coding, except for the VBA for hyperlinking. Thank you
Thanks for your question. Yes, I understand that VBA code is overwhelming if you haven't learned it before.
I looked for a formula-only approach. But I haven't found a method with Formulas that works to create a list of sheet names that are hyperlinked.
I tried wrapping the HYPERLINK() function around the GET.WORKBOOK formula, but that doesn't work because it only hyperlinks to the first sheet and the remaining sheets don't get links.
➡️ If anyone reading this has a way that works, please share it here!
BTW if you want to learn VBA from the ground up, I have a highly rated course that takes you step by step: www.launchexcel.com/shop
I found a method from ExcelJet that could be what you're looking for: exceljet.net/formulas/link-to-multiple-sheets
It uses helper columns to create a list of sheet names and cells, then uses the HYPERLINK() function to create hyperlinks. I hope this works for you!
@@launchexcelThank you for the help, I will look into it.
Hi Victor, It's ZS again :) This solution is perfect. I will certainly give it a go but I need a small help. When listing the Sheets, could these be sorted A-Z? My sheet names are Alpha numeric and sorted list would be great. I have no experience of VBA and hoping to embark on the learning journey and makinng my work a bit easier.
Hey ZS! I'm working on a new VBA course and will cover projects like sorting sheets. So you'll be able to learn VBA from the ground up and automate your work.
In the mean time, you can check out www.ablebits.com/office-addins-blog/alphabetize-tabs-excel/ (I'm not affiliated with them but I find their tutorial helpful)
@launchexcel thanks Victor, I will check the link over the weekend. Also when will you be starting the course?
I'm planning the new course outline now.
If you give me a list of the top 3+ things you'd like to accomplish with VBA, I'll put them into the course outline. This will help me to choose what to include and to leave out.
(For example: sending emails with VBA, making PPTs from Excel, automate reporting, working with charts, working with pivot tables, automate Power Query, etc.)
It would be nice if the pages were sorted from A to Z or Smallest to Largest
Thanks for the suggestion. I'll add that to my list of videos to make. I agree it's helpful to have the worksheets sorted.
Level 11 - Alt w k
Interesting, I tried this but didn't work for me. What happens when you do this?
I did find ALT W N → New window
Source: www.reddit.com/r/excel/comments/jrkhr1/tired_of_flitting_back_and_forth_within_tabs/
@@launchexcel
Opens Navigation Pane
can be found View | Show
saved the workbook as new workbook but now I just get #BLOCKED! as a result instead of the sheet name.
If this is an error with get.workbook(1), you might need to check if you allowed macros to work.
*Q&A on listing sheet names coming up as block:*
answers.microsoft.com/en-us/msoffice/forum/all/excel-list-sheet-names-coming-up-as-blocked/2167f4df-bff2-470b-96be-3797267a9195
*Microsoft page on how to correct a **#BLOCKED**! error:*
support.microsoft.com/en-us/office/how-to-correct-a-blocked-error-13be117b-92e4-400a-a215-aa59d37d6e7c
No offense, but from the get-go, i don't like the idea of VBA. In the last 7 or 8 years, i dont remember having to use it even once given the level of abstract programming that can be done with the tools and data structures accessible on an excel spreadsheet. Even writing a bona-fide stochastic simulation or programming a statistical analysis package.
Thanks for your comment, no offense taken 😀.
I agree, VBA isn't for everyone. But those who use VBA know it has powerful use cases. Particularly in report automation and controlling other Office applications. I think those are the areas where VBA shines.
too complicated
Thanks for your feedback. If you need something easier, I suggest starting with a beginner's Excel tutorial.
Great video, Victor. Thanks a lot!!
In my situation, the GET.WORKBOOK function is resulting in the "#NAME?" error. I'm using Excel 365, do you have any idea what could be going on?
hi @eduartopimentel I suggest you try enabling Excel 4.0 macros. That might fix the issue.
Here is Microsoft's support page: support.microsoft.com/en-gb/office/working-with-excel-4-0-macros-ba8924d4-e157-4bb2-8d76-2c07ff02e0b8
➡️ Steps:
1. Click the File tab, and then click Options.
2. Click Trust Center, and then click Trust Center Settings.
3. Click Macro Settings, and then select the Enable Excel 4.0 macros when VBA macros are enabled check box. You can then select Disable VBA macros with notification or Enable all macros (not recommended; potentially dangerous code can run).
4. Click File Block Settings and then, under File Type, select Excel 4 MacroSheets and make sure that the check box for Open is selected.
5. Under Open behavior for selected file types, click Open selected file types in Protected View and allow editing.
6. Click OK twice.