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


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 29 comments
Carl - November 14, 2018

It’s so helpful thankss

Carl - November 14, 2018

It worksssss!! thanks for your help.

Steven - October 4, 2018

Hi All, I am a novice with VBA and am a little stuck..

I have been finding codes and altering them to suit my needs but am a little unlearned in VBA to write a code for my next hurdle (nor can I find anything similar to alter).

I have a workbook that has 167 rows of data per column x 100 columns. Each of these columns represent an individual product with exactly the same formulas embedded in each cell.

From that, each product I am breaking the data down to show pricing per product. here is where it gets tricky for me.

Each product has its own sheet name (“Item 1” through to “Item 100”) which all need to be hidden but able to be un hidden based on two references.

Within another sheet (“Customer Details & Contract $”) manual selection of Quantity in cell “D19” is 1-100, command button enter takes back to home page and then

on sheet named “home page” im looking to enter items based on command button that unhides and shows Item 1 sheet first (with all others still hidden), then when command button clicked it needs to hide that sheet 1 and show sheet 2 only and then loop through 1 at a time with that same command button selection up to the quantity amount selected in “D19” of (“Customer Details & Contract $”). eg if 50 quantity the code loops only up to 50 and not through the whole 100.

I hope I am explaining the process in a way it can be followed/understood!!???

ANND im hoping someone can help or give me some advice to achieve what im trying to do.

Cheers in advance

Anthony C Sciscioli - August 31, 2018

I am trying to figure out a way to unlock a hidden or locked worksheet by creating an action on the previous worksheet. Is this possible?

Donald - August 6, 2018

Excellent! Thanks so much

Jeremy - May 17, 2018

This tip has been a lifesaver and then the macro! Wonderful.

Tim - May 16, 2018

Hi Jon,
Can you tell how to do a group tab, Expanding/Collapsing? I want to create Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec. tab. Each month tab will have 5 sheets. If i click Jan it will expand the 5 sheets, click again will collapse. Do you know how can i do it? please help. Thank you

Private Sub Worksheet_Activate()
Dim sheet As Worksheet
Application.ScreenUpdating = False
If Feb.Name = “Show Feb” Then
‘Make all sheets visible
For Each sheet In ThisWorkbook.Sheets
sheet.Visible = xlSheetVisible
Next sheet
‘Change the sheet name to the “Collapse” name you want
Feb.Name = “Hide Feb”
‘Pick a sheet to display after the once hidden sheets are expanded
‘Hide all sheets except the one’s you want to keep visible
For Each sheet In ThisWorkbook.Sheets
If (sheet.Name Jan.Name And sheet.Name Jan.Name) Then
sheet.Visible = (sheet.Name Sheet6.Name And sheet.Name Sheet7.Name And sheet.Name Sheet8.Name And sheet.Name Feb.Name)
End If
Next sheet
‘Change the sheet name to the “Expand” name you want
Feb.Name = “Show Feb”
‘Pick a sheet to display after the sheets to be hidden are collapsed
End If
Application.ScreenUpdating = True
End Sub

Peter Dandy - May 7, 2018

Alternatively, you can Alt-F11 to open VBA window, select each sheet in turn in teh Project window and double click the “visible” field in the properties window. This should select between xlSheetVisible, xlSheetHidden and xlSheetVeryHidden.

You still have to do it one by one but it’s quicker than right clicking on a tab, selecting Unhide, scrolling to the sheet that you’re interested in and double-clicking it, only to start the process for the next one.

Mickael - May 2, 2018


Thank you.
How does the immediate window know to dim ws as a worksheet without establishing that variable?

Jeff - March 27, 2018

Wow! Thanks once again for making our lives easier (and making us look better). This stuff is way too much fun!!!

What simple but very useful code. I changed the macro slightly using Left(ws.Name, 5) = “Table” to unhide all worksheets that start with “Table” so worksheets that might contain the text “Table” but not at the beginning would stay hidden.

I really appreciate Excel Campus and the work you’re doing!


Anika - February 14, 2018

Many thanks for the VBA code!

CJV - December 31, 2017

Jon, my hidden worksheets are hidden for security, not just appearance. I have password-protection. Is there a programmatic way in VBA to unhide a list of sheets without invoking multiple password prompts? Thinking maybe with a Custom View where Excel would know to ask only one time?

emmy - December 30, 2017

Hello , thank you for th tuto it is useful.

my question is : I personalized my ribbon and ad a navigation button to allow me navigate from sheet to sheet ,,, in the same time I want to hide the sheets and a lower menu and show them in the new ribbon navigation….

could yoou help plz ?

thnak’s a lot

best regards

Andrea - October 11, 2017

Hello Jon! your site is great! I have tried all the Macros you recommend to unhide one sheet, but none worked. The hidden sheets contains formulas I need to copy; the spreadsheet is not mine. Thanks!

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

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!

Sophia - January 24, 2017

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

Val Klimer - January 10, 2017


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

nancmadi - January 4, 2017


Wade Galt - December 6, 2016

AWESOME! Thank You

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?

    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
                ws.Visible = False
            End If
        Next ws
    End Sub

    I hope that helps.

Lorrie - September 29, 2016

I just used option 1 – perfect. Thanks!!

    Jon Acampora - October 4, 2016

    Awesome! Thanks for letting me know Lorrie! 🙂

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

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

MsgBox (“All sheets are now visible”)

End Sub

    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!


Leave a Reply: