Show Specific Sheets in Excel

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.

showsheetsmacro01

If you choose ALL as the Sheet Type, all the sheets in the workbook are made visible.

showsheetsmacro02

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.

showsheetsmacro04

On the Menu sheet, the drop down list is based on that named range, and the cell with the drop down is named SelectType

showsheetsmacro05

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.

showsheetsmacro03

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.

showsheetsmacro06

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

_____________________

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.