3 Ways to Unhide Multiple Sheets in Excel + VBA Macros
15

3 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??

As you probably know, you cannot unhide two or more 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, 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 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.

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.

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

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

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

#3 – 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.

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.

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.

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 using 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 checkout 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!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 15 comments
https://undercabinetradio.tech - September 1, 2017

Good day very nice site!! Man .. Beautiful .. Wonderful ..
I’ll bookmark your website and take the feeds also? I am satisfied to find
a lot of useful information here in the publish, we need develop more techniques in this regard, thanks for sharing.
. . . . .

Reply
John - February 8, 2017

Just wanted to give a BIG thank you to Jerry. I am editing a workbook created by someone who’s no longer at our company and could not figure out how to keep the current sheet active when checking boxes to unhide other sheets. I’ve literally been looking for an answer for days, and low and behold, here it is. The bookmark makes things work exactly as desired.

Thanks again!

Reply
Sophia - January 24, 2017

The first suggestion helped so much! I use Excel 2016. Thank you!!

Reply
Val Klimer - January 10, 2017

Jon,

Thank you, used first option and worked great… thank you for your expertise and straight forward solutions, like the bookmark too Jerry!

Make it a great day! VMK

Reply
nancmadi - January 4, 2017

THANK YOU!!!

Reply
Wade Galt - December 6, 2016

AWESOME! Thank You

Reply
Abbie - November 30, 2016

Hi Jon.

I need help. I want to create a macro where if the cell value is lets say “Calculation Method 1” and i click on a button beside it “Calculate” then it will show only the worksheet specific for calculation method 1. and if “Calculation Method 2” then worksheet for method 2 and so on.

How can I do that?

Reply
    Jon Acampora - December 1, 2016

    Hi Abbie,

    If the sheet name is the same as the calculation method name then you could do something like the following. This assumes the calculation method is in cell A1. You can modify to fit your needs.

    Sub Unhide_Multiple_Sheets()
    Dim ws As Worksheet
        
        For Each ws In ActiveWorkbook.Worksheets
            If ws.name = Range("A1").Value 
                ws.Visible = xlSheetVisible
            Else
                ws.Visible = False
            End If
        Next ws
    
    End Sub

    I hope that helps.

    Reply
Lorrie - September 29, 2016

I just used option 1 – perfect. Thanks!!

Reply
Jerry Cook - January 21, 2015

Hey Jon! Happy New Year! And GREAT STUFF! Keep it up!

Here’ another variation of the Unhide All with a couple totally unnecessary bells and whistles I assembled a few years back to help the boss out…

Assign the macro to a new button in your ribbon or QAT for easy access.

Oh, and the Bookmark feature works great in any multi-sheet VBA operation to bring you back to the worksheet you started the macro from.

Cheers! Jerry

Sub UnhideAllWorksheets()

Dim i As Long, j As Long, ws As Long, hidden As Long
Dim Bookmark As Object
Set Bookmark = ActiveSheet
hidden = 0
ws = ActiveWorkbook.Sheets.Count
For i = 1 To ws
If Sheets(i).Visible = False Then hidden = hidden + 1
Next

If MsgBox(“There are ” & ws & ” total sheets in this workbook.” & vbCrLf & vbCrLf & _
“Do you want to expose the ” & hidden & ” hidden sheets?”, _
vbQuestion + vbYesNo) vbYes Then Exit Sub

For j = 1 To ws
Sheets(j).Visible = True
Next

MsgBox (“All sheets are now visible”)

Bookmark.Activate
End Sub

Reply
    Jon Acampora - January 21, 2015

    Thanks Jerry! This is great! I like that bookmark feature. If anyone is wondering, the bookmark feature basically remembers the sheet you started on, and then selects that sheet again at the end of the macro, after the unhide loop. Since the code is looping through all the sheets and unhiding them, it typically ends on the last sheet that is unhidden. This means you will be viewing the last unhidden sheet when the loop is complete. To stay on the sheet you start on, Jerry has created a variable named “Bookmark” in his macro. He sets the variable as the activesheet before the unhide loop is run, to store it in memory, then selects the sheet at the end of the macro with the “Bookmark.Activate” line.

    Thanks again for sharing Jerry!

    Reply

Leave a Reply:

Free Webinar: The 5 Secrets to Understanding Pivot Tables

x