4 Ways to Unhide Multiple Sheets in Excel + VBA Tutorial

Bottom line: Learn a few different ways to unhide (show) multiple sheets at the same time with a VBA macro or add-in.

Skill Level: Intermediate

Cannot Unhide Multiple Sheets in Excel??

Depending on which version of Excel you are using, you might not be able to unhide two or more sheets at the same time.  If you are NOT using Microsoft 365, then the Unhide menu only allows you to select one sheet at a time.

This can make the process of unhiding multiple sheets very time-consuming, especially if you want to unhide all the sheets in the workbook.  So in this article, we will look at a few different ways to quickly make sheets visible.

#1 – Use Microsoft 365

In 2021, Microsoft released an update for Excel that now allows you to unhide multiple sheets at the same time.

This is the ultimate solution and I'm happy that Microsoft fixed this issue.

If you aren't on the latest version of Excel yet, then the next three solutions will help you with automating the process of unhiding multiple sheet tabs.

#2 – Use the VBA Immediate Window to Unhide All

The fastest way to make all the sheets visible in Excel is to use a macro (VBA).  The following line of VBA code uses a For Next Loop to loop through each sheet in the active workbook and make each sheet visible.

For Each ws In Sheets:ws.Visible=True:Next

You can run this code in the VB Editor's Immediate Window in three easy steps:

1. Alt+F11 (opens the VB Editor Window)
2. Ctrl+G (opens the Immediate Window)
3. Paste the following line of code in the Immediate Window and press Enter
   For Each ws In Sheets:ws.Visible=True:Next

The screencast below shows how to implement these steps.

That line of code loops through all the worksheets in the workbook and sets each sheet's visible property to “True”.  This makes each sheet visible, which is the same as unhiding each sheet.

The colon character “:” used in the code allows you to basically combine multiple lines of code into one line.  This makes it possible to run in the Immediate Window because the Immediate Window only evaluates one line of code at a time.

Check out my article on 5 Ways to Use the VBA Immediate Window to learn more.  And a big thanks to Jan Karel Pieterse of www.jkp-ads.com for sharing this trick in the comments section.

#3 – Use a Macro to Unhide Multiple Sheets

If you are scratching your head at that line of code in #1, this section should help explain it better.

The macro below is basically that same line of code, but it is broken up into multiple lines.  This makes it much easier to read and understand.

Sub Unhide_Multiple_Sheets()
Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
        
        ws.Visible = xlSheetVisible
    
    Next ws

End Sub

Download the file that contains the macro.

The lines in the code above that start with “For” and “Next” represent a For-Next Loop Statement.   The first line “For Each ws In ActiveWorkbook.Worksheets” tells the macro to loop through each worksheet in the worksheets collection of the workbook.

That line also references the variable “ws” and sets it to represent the worksheet object.  This means that “ws” temporarily represents the current worksheet in the loop.

When the “Next ws” line of code is hit, the macro jumps back up to the first line of code within the loop and evaluates it again.  It continues to loop through all the sheets in the workbook's worksheet collection (Activeworkbook.Worksheets).

We can then use “ws” inside the loop to change the current worksheet's properties.  In this case, we are setting the “Visible” property of the sheet to be visible (xlSheetVisible).  The visible property has three different properties to choose from:

• xlSheetHidden
• xlSheetVeryHidden
• xlSheetVisible

You can also set the property to “True”, which works the same as xlSheetVisible.

Here is the documentation on the VBA Visible property from Microsoft.  And checkout my article on the For Next Loop for a detailed explanation of how it works.

Unhide Sheets That Contain a Specific Name

What if we only want to unhide the sheets that contain the word “pivot” in the sheet name?

We can add a simple IF statement to the macro to only unhide sheets that contain a specific name or text.

Sub Unhide_Sheets_Containing()
Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
        If InStr(ws.Name, "pivot") > 0 Then
            ws.Visible = xlSheetVisible
        End If
    Next ws

End Sub

Download the file that contains the macro.

The InStr function searches for text in a string and returns the position of the first occurrence of the text.  It is short for InString, and the function is similar to the SEARCH or FIND functions in Excel.

So in this case we are looking for any sheet that contains the word “pivot” in the sheet name.  The “ws.name” reference returns the name of the worksheet that is currently being evaluated in the For-Next loop.

So this line “If InStr(ws.Name, “pivot”) > 0 Then” basically says, if the word “pivot” is found in the sheet name then the Instr function will return a number greater than zero.  The IF statement will evaluate to True and the next line of code will be run to unhide the sheet.

If the word “pivot” is NOT found in the sheet name, then the IF statement will evaluate to False and the code will skip all lines until it gets to the “End If” line.  Therefore, the sheet that is currently being evaluated in the loop will keep its current visible property (visible or hidden).

This macro works great if you are hiding and unhiding sheets every day/week/month for a report that you maintain.  Run the macro to unhide specific sheets when you open the workbook.  After you are finished, run the same code but change the visible property to xlSheetHidden to re-hide the sheets (you can create a new macro for this).

#4 – Use Tab Hound or Tab Control

If all this code is making your head spin, the Tab Hound Add-in allows you to unhide multiple sheets with the click of a few buttons.

The screencast below shows how simple this is.

The Tab Hound window contains a list of all the sheets in the workbook.  You can select multiple sheets in the list, then press the Unhide button to unhide all the selected sheets.

This makes the process of unhiding multiple sheets really fast!

Tab Hound also contains additional ways to filter the sheet list.  You can type a search in the search box, filter for all visible or hidden tabs, and even filter by tab color.  This makes it easy to find the sheets you are looking for and then perform actions on them like hiding/unhiding.

This video also shows how to quickly hide and unhide multiple sheets with Tab Hound.

Click here to learn more about Tab Hound

Publishing Reports? Tab Control Will Save You Time

If you are producing weekly or monthly reports, and want to make sure all the right sheets are hidden before you send it out, the Tab Control add-in can save you a lot of time.

Here is a scenario that we commonly face…

We need to update a workbook with new data this week and make some changes before emailing it out. Those updates require us to unhide a few sheets, make the changes, then hide the sheets again. It can be a time-consuming process if you have to hide/unhide a lot of sheets.

Tab Control will set up a control sheet in your workbook with a list of all the worksheet names. You can set the visible property for each sheet in the list, then click a button to hide or unhide all the sheets based on the list.

This means you can set up the control sheet to a specific view, so only specific worksheets are visible. Click the “Run Update” button and all sheets will be hidden/unhidden based on your control sheet. This ensures that your report will show the correct sheets every time you send it out, and make you look really organized. 🙂

The Tab Control add-in is included with Tab Hound.

Click here to learn more about Tab Hound

Conclusion

Unhiding multiple sheets at the same time in Excel will require code or a macro. There is one other way to use Custom Views, but it has limitations if you use Excel Tables (and I love Tables).

Hopefully, you learned some VBA code that you can implement. You can also add the macros to your Personal Macro workbook to run them anytime you need.

If coding isn't your thing then check out the Tab Hound add-in.  It will save you time and make your life a lot easier. (win-win!) 🙂

Please leave a comment below with any questions. Thanks!