In a workbook with lots of worksheets, it can be hard to find the ones that you need, to get a specific task done. Instead of scrolling through all the sheet tabs, or using the popup list of worksheets, use a drop down list to show just a few specific sheets in Excel. You’ll be able to focus on what you need to do, and ignore everything else.
Select a Sheet Type
On the Menu sheet, there is a data validation drop down list. Select a sheet type from the drop down list, and only the Menu sheet, and sheets with the selected text in their name are visible.
For example, choose “computer” and all sheets with “computer” in their name are visible, along with the Menu sheet. All other sheets are hidden.
If you choose ALL as the Sheet Type, all the sheets in the workbook are made visible.
The Drop Down List
On another worksheet, there is a list of sheet types, formatted as a named Excel table. The data in that table was selected, and named as SheetTypes.
On the Menu sheet, the drop down list is based on that named range, and the cell with the drop down is named SelectType
You could add more items to the SheetTypes list, and the list will expand automatically to include them. Sort the list A-Z, after adding new items – there is a space character at the start of “ ALL”, so it will always sort to the top of the list.
The Menu Worksheet Code
On the Menu sheet, there is Worksheet_Change code, that runs when any change is made to the sheet. If the SelectType cell was changed, a macro runs. If “ ALL” was selected, a macro runs to show all the sheets. If a different type was selected, it runs a macro to show only the sheets with that text in their name. The Menu sheet is left visible too.
Download the Sample File
To test the macros, you can download the sample file with the code to show specific sheets in Excel. Go to the Excel Files page on my website, and in the UserForms section, look for UF0034 – Show Specific Sheets
Video: Show Specific Sheets in Excel
Watch this video to see how the sheet selector works, and for a brief explanation of the VBA code to show specific sheets in Excel.
0:00 Introduction
0:19 Select a Sheet Type
0:56 Set Up the List
1:55 Find a Command
2:17 View the VBA Code
3:29 View the Macro Code
4:59 Get the Sample File
_____________________
Hi! How do I edit this code so more than just the Menu tab stay visible? I have three tabs I need to stay visible and then all others are dynamic based on what I select in the drop down on the Menu tab.
Thanks!