Show Specific Sheets in Excel
In a workbook with lots of worksheets, you can have a hard time finding the sheets that you need, to do a specific task. Instead of scrolling through all the sheet tabs, or using the popup list of worksheets, use a drop down list to show specific sheets in Excel. Also, all other sheets are hidden, and 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. So, just 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 the other sheets are hidden, because their names don't match.
Also, if you choose ALL as the Sheet Type, all the sheets in the workbook are visible again.
The Drop Down List
On another worksheet, I made a list of sheet types, and formatted the list as a named Excel table. After that, I selected the data in that table, and named it as SheetTypes.
On the Menu sheet, the data validation drop down list is based on that SheetTypes named range, so it shows all the items in that list. Also, 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. So, if you change the SelectType cell, a macro runs.
- If you select “ ALL”, a macro runs to show all the sheets.
- However, if you select a different type, a macro runs, to show only the sheets with that text in their name. The macro leaves the Menu sheet visible too, so you can select from the drop down again..
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
NOTE: If you need to show and hide sheets in several workbooks, the Tab Hound add-in, from Excel Campus, has features that make the task easy.
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: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