Quickly find specific sheets, by selecting a sheet type from a drop down list. Other sheets hidden. Select "ALL" to see all sheets again.
Instead of scrolling through all the sheet tabs, in a workbook with lots of worksheets, use a drop down list to show specific sheets. All other sheets are hidden, except the Menu sheet, so you can quickly find what you need. Watch this video to see how it works, and the written instructions and sample file download link are below.
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
Transcript: The full transcript for this video is at the bottom of the page.
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.
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 can add more items to the SheetTypes list, and the list will expand automatically to include them. Then, 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.
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.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Range("SelectType").Address Then Select Case Target.Value Case " All" ShowAllSheets Case "" 'do nothing Case Else ShowSelSheets 'do nothing End Select End If End Sub
The following macros are stored in a regular code module.
The ShowAllSheets macro runs when "ALL" is selected, and makes all the sheets visible.
Sub ShowAllSheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Sheets ws.Visible = xlSheetVisible Next ws End Sub
The ShowSelSheets macro runs if any other option is selected, and it shows only the Menu sheet, and any sheet that has the selected text in its name.
Sub ShowSelSheets() Dim ws As Worksheet Dim strType As String strType = Worksheets("Menu").Range("SelectType").Value For Each ws In ActiveWorkbook.Sheets If InStr(1, ws.Name, strType) > 0 Then ws.Visible = xlSheetVisible Else If ws.Name <> "Menu" Then ws.Visible = xlSheetHidden End If End If Next ws End Sub
Instead of creating separate macros to show all the sheets, and to show the selected sheets, you could use the following WorksheetChange procedure, submitted by Leonid Koyfman.
It has the following differences from the original code:
Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim strType As String If Target.Address = Range("SelectType").Address Then strType = Target.Value If strType = "" Then Exit Sub End If For Each ws In ThisWorkbook.Sheets If InStr(1, " " & ws.Name & " ", " " _ & strType & " ", vbTextCompare) > 0 _ Or strType = "(All)" Then ws.Visible = xlSheetVisible Else If ws.Name <> "Menu" Then ws.Visible = xlSheetHidden End If End If Next ws End If End Sub
Here is the full transcript from the Show Specific Sheets video:
In this workbook, we have quite a few worksheets and I've set up a drop down list that we can use to hide everything except a specific kind of worksheet.
I'll show you how that works and then we can look at the code quickly in the background.
This is the menu sheet and there's a cell here that has a drop down list. If I click that, I can see, Computer, P&L and ALL.
I'll click on Computer and now we just have the menu sheet showing and the two sheets that had computer in their names.
I'll do the same for P&L, so there are the two sheets that are P&L and now I'll go back to looking at all the sheets and all the sheets, the admin sheets and instructions and everything is visible now.
To set this up, we have a data validation drop down list here, if I go to the data tab, data validation, we can see that there's a list and it's based on sheet types.
So I'll close that and that sheet types is on this admin list, I've just entered three items here, I typed all and I left a space character at the beginning of that, just so if I sort this list, that item will always go up to the top, so it's easy to find.
Then once the list was entered on this sheet, I created a table from that, so on the home tab, you can format as a table and select one of these styles for it and that means that now if I put another item at the bottom of this list, the list will expand automatically, we can see that the list is now grown and I can sort this.
And now if I go back to the menu, we've got admin and that just shows any sheet that has admin in its name.
So it's a quick way to work with your sheets if you want to either see a whole lot of them or just a small selection of your sheets so you can focus on those.
The sheets that are visible, change because a macro runs, if we make a change in this cell and to see that code, I'm going to right click on the menu tab, view code, and here is the worksheet change event.
So it's going to look at the target and that's the changed cell.
If the address of that cell is the same as the select type address and if we look at this cell, and look up in the name box here, that cell is named selecttype.
If we've changed that cell, then we're going to see what the value of the target cell is.
If we select it all, you can see this space there, it's going to run a macro called, showallsheets.
If we just clear out that cell, then nothing's going to happen, we can't show sheets if we don't have the text that's in the name of those sheets.
And, if they didn't select all or the cell isn't empty, then it's case else, so for every other situation, we're going to run a macro called, ShowSelSheets.
To see those macros, I'm going to right click here, definition, and that takes us here into module 1 and we can see the ShowAllSheets and there's a variable, ws, which is a worksheet.
So for each worksheet in the active workbook sheets, that worksheet, visible property is visible.
So every sheet becomes visible and for the other macro, ShowSelSheets, we again have a variable for the worksheet and one for the string, the str type, so whatever type we've selected here on the menu sheet in that select type range, so whatever that value is, that's what we're going to be looking for.
For each worksheet, in the ActiveWorkbook sheets, if we can find that name, so the strType in the worksheet name and we're using the InStr, the in string function for that, so if it does find that string in the name, then the sheet is visible, otherwise, unless the sheet is named, menu, we want to leave that visible, then that sheet will be hidden.
Those are the two different macros that will run based on making a change here.
Single Menu: To test the macros from this page, get the Specific Sheet Selector sample file with the code to show specific sheets in Excel. The workbook is in xlsm format, and contains macros
Single Menu - Charts: If your workbook has chart sheets, in addition to worksheets, get the Specific Sheet Selector Charts sample file with the code to show specific sheets and chart sheets, in Excel. The workbook is in xlsm format, and contains macros.
NOTE: You don't need this file if your workbook only has charts that are on worksheets (embedded charts).
Multi Menu: This Multi Menu sample file works like the Single Menu example, but there are 3 sheets with a sheet type selector drop down, instead of just one menu. The code is slightly different, so get this Multi Menu sample file, to see how it works. The workbook is in xlsm format, and contains macros
Last updated: September 4, 2021 11:05 AM