microsoft office excel

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

As you probably know, you cannot unhide multiple sheets at the same time in Excel.  The Unhide menu only allows you to select one sheet at a time.

Cannot Unhide Multiple Sheets

This can make the process of unhiding multiple sheets very time consuming.  So in this article we will look at a few different ways to quickly make sheets visible.

1. Easy Way – 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 will 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.

Unhide All Excel Worksheets with the VBA Immediate Window

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.

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

Unhide Multiple Sheets Macro.xlsm (64.2 KB)

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.

The Unhide Mulitple Sheets in Excel VBA Macro Explained

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

Excel VBA Worksheet Visible Property Enumerations

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.

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.

Unhide Multiple Sheets Macro.xlsm (64.2 KB)

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 “” 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).

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

The screencast below shows how simple this is.

Unhide Multiple Sheets with the Tab Hound Add-in for Excel

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.

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.

Quickly Hide and Unhide Multiple Sheets with Tab Control Add-in

Tab Control will setup 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 setup 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 everytime you send it out, and make you look really organized. ?

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




Don’t forget to like us on Facebook and Google+ as well as share this to your social networks to support us.

Please comment below if you have any query.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published.