List All Your Sheets Efficiently in Excel (10 Levels)

Подписаться 32 тыс.

Просмотров 51 тыс.

50% 1

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.

Хобби

Опубликовано:

27 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 49

@launchexcel 3 месяца назад

*❤ 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

@ExcelOffTheGrid 3 месяца назад

Welcome back Victor - it's nice to see some new videos from you.

@launchexcel 3 месяца назад

Thanks Mark!

@bintousidibe6843 26 дней назад

Thanks a lot !!!

@launchexcel 26 дней назад

You are welcome!

@markhuang368 3 месяца назад

Thank you Victor for the excellent video.

@launchexcel 3 месяца назад

Hey Mark, thanks for the comment 😄

@arunpremkumar3920 Месяц назад

Thanks A lot

@launchexcel Месяц назад

You're welcome 😄

@TechMe.79 3 месяца назад

Impressive. Thanks for the video

@launchexcel 3 месяца назад

Sure, glad you like it

@hichamhadj9640 3 месяца назад

Excellent video, especially the boss level part

@launchexcel 3 месяца назад

Thanks for your comment. Glad you like the boss level 👍

@myszek512__6 3 месяца назад

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.

@launchexcel 3 месяца назад

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.

@planningpedia 3 месяца назад

Great help to learners.. can we make list from another workbook also?

@launchexcel 3 месяца назад

@@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")

@azlanm0305 2 месяца назад

Hai Victor,,.. excellent video... if you could just advise on how to insert macro command on how to position the Navigation button thank you

@launchexcel 2 месяца назад

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.

@robbe58 3 месяца назад

Nice video. Please keep us informed when the download file is available. Thank you.

@launchexcel 3 месяца назад

Thanks Rudi! Yes I'll get the download file ready with the article. Links coming soon 😄

@drog2000 3 месяца назад

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

@launchexcel 3 месяца назад

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

@launchexcel 3 месяца назад

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!

@drog2000 3 месяца назад

@@launchexcelThank you for the help, I will look into it.

@fevziciddi2948 3 месяца назад

It would be nice if the pages were sorted from A to Z or Smallest to Largest

@launchexcel 3 месяца назад

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.

@zs9853 2 месяца назад

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.

@launchexcel 2 месяца назад

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)

@zs9853 2 месяца назад

@launchexcel thanks Victor, I will check the link over the weekend. Also when will you be starting the course?

@launchexcel 2 месяца назад

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.)

@ciaucia156 3 месяца назад

Level 11 - Alt w k

@launchexcel 3 месяца назад

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/

@ciaucia156 3 месяца назад

@@launchexcel Opens Navigation Pane can be found View | Show

@TheAhmedrty Месяц назад

saved the workbook as new workbook but now I just get #BLOCKED! as a result instead of the sheet name.

@launchexcel Месяц назад

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

@tammyl.9254 3 месяца назад

too complicated

@launchexcel 3 месяца назад

Thanks for your feedback. If you need something easier, I suggest starting with a beginner's Excel tutorial.

@whimpypatrol5503 2 месяца назад

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.

@launchexcel 2 месяца назад

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.

@eduardopimentel7858 13 дней назад

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?

@launchexcel 8 дней назад

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.