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

7 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

  3. Gerrie says:

    Hi

    I am working on a document where I have 12 drop down lists and each drop down have 5 items in the list.
    I love this so much as it is exactly what I am looking for however I am getting stuck on the 1st drop down, which I might add works perfectly.
    So I want to duplicate the code for the 2nd, 3de, 4th… drop downs that will only show me one item of the 5 option on the page.

    So here is what I am looking for exactly, If I select an item (coffee) from the 1st drop down it should display that sheet (coffee) then if I select an Item from the 2nd drop down (stationary) it should also display that sheet (Stationary) so now I should have 3 sheets visible. Menu, Coffee & Stationary.

    And this is where I get stuck I can’t get the code to display the 3de sheet, If you want I can email you the workbook I have and you can see exactly what I am working on and struggling with.

  4. Gerrie says:

    Hi Debra
    I have a unique workbook and formula situation. so let me get started with the scenario:
    I have a couple drop downs (x3)
    Expansion1 (each drop down has 5 options to pic from Phone8 (1), Phone16 (1), Phone 30 (1), DS16 (1), DS30 (1))
    Expansion2 (each drop down has 5 options to pic from Phone8 (2), Phone16 (2), Phone 30 (2), DS16 (2), DS30 (2))
    Expansion3 (each drop down has 5 options to pic from Phone8 (3), Phone16 (3), Phone 30 (3), DS16 (3), DS30 (3))

    I currently have a formula that runs and it works really well, first off I hide all my sheets except the Main sheet

    Sub ShowAllSheets()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Sheets
    ws.Visible = xlSheetVisible
    Next ws
    End Sub

    __________________________________________________________________________________________________________
    Sub ShowSelSheets()
    Dim ws As Worksheet
    Dim strType As String
    strType = Worksheets(“Main”).Range(“Expansion1”).Value
    For Each ws In ActiveWorkbook.Sheets
    If InStr(1, ws.Name, strType) > 0 Then
    ws.Visible = xlSheetVisible
    Else
    If ws.Name “Main” Then
    ws.Visible = xlSheetHidden
    End If
    End If
    Next ws

    End Sub

    Next when I select an object from expansion1 Drop-down list it only displays the selected item and that is exactly what I want, however when I select an item from drop-down list 2 or 3 I am not seeing any of the selected items.

    Sub ShowSelSheets()
    Dim ws As Worksheet
    Dim strType As String
    strType = Worksheets(“Main”).Range(“Expansion1”).Value
    For Each ws In ActiveWorkbook.Sheets
    If InStr(1, ws.Name, strType) > 0 Then
    ws.Visible = xlSheetVisible
    Else
    If ws.Name “Main” Then
    ws.Visible = xlSheetHidden
    End If
    End If
    Next ws

    End Sub

    So I have tried to amend the formula (listed below)

    Sub ShowSelSheets()
    Dim ws As Worksheet
    Dim strType As String
    strType = Worksheets(“Main”).Range(“Expansion1”).Value
    For Each ws In ActiveWorkbook.Sheets
    If InStr(1, ws.Name, strType) > 0 Then
    ws.Visible = xlSheetVisible
    Else
    If ws.Name “Main” Then
    ws.Visible = xlSheetHidden
    End If
    End If
    Next ws

    strType = Worksheets(“Main”).Range(“Expansion2”).Value
    For Each ws In ActiveWorkbook.Sheets
    If InStr(1, ws.Name, strType) > 0 Then
    ws.Visible = xlSheetVisible
    Else
    If ws.Name “Main” Then
    ws.Visible = xlSheetHidden
    End If
    End If
    Next ws

    End Sub

    However then I am not seeing my expansion1 selection any more and I can now see the selection I have made in expansion2.

    My end result should be that I see the Main sheet and any other selected options/sheets for expansion1, expansion2 and/or expansion3.

    Thanks for your assistance in advance.

    • Hi Gerrie,
      Thanks for the detailed explanation of what you’ve done so far — that makes it easier to help you!
      You could change the code on the Main sheet’s code module. Try something similar to this:
      –for the “SelectType” cell, it shows all the sheets, or the selected sheet type
      –for the other drop downs, it doesn’t hide any sheets, and shows the sheet with the name that was selected in the active cell

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim lDV As Long
      On Error Resume Next
        If Target.Address = Range("SelectType").Address Then
          Select Case Target.Value
            Case " All"
              ShowAllSheets
            Case ""
              'do nothing
            Case Else
              ShowSelSheets
              'do nothing
          End Select
        Else
          lDV = Target.Validation.Type
          If lDV = 3 Then
            Sheets(Target.Value).Visible = xlSheetVisible
          End If
        End If
      End Sub

Leave a Reply

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