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.

showsheetsmacro01

Also, if you choose ALL as the Sheet Type, all the sheets in the workbook are visible again.

showsheetsmacro02

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.

showsheetsmacro04

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

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

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

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: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

_____________________

Save

You may also like...

4 Responses

  1. prashant99 says:

    Very useful, Missing excel roundups…

  2. sam says:

    @Debra

    You can do this with one line of Code

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range(“SelectType”).Address Then ActiveWorkbook.CustomViews(Target.Value2).Show

    End Sub

    You need to Define 3 Views All, Computer, P&L

Leave a Reply to sam Cancel reply

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