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!

31 comments

Your email address will not be published. Required fields are marked *

  • Using the InStr works well. However, I want it to look for two terms

    “863” and “Notes” so it will unhide all the sheets beginning with 863 and the sheet named “Notes”

    The code below works if I don’t have ,”Notes” in it. When I add ,”Notes” it fails.

    Sub Unhide_Sheets_Containing863()
    Dim WS As Worksheet

    For Each WS In ThisWorkbook.Worksheets

    If WS.Name ActiveSheet.Name Then WS.Visible = xlSheetHidden
    Next WS ‘Hides all the visible sheets bar the active sheet

    For Each WS In ActiveWorkbook.Worksheets
    If InStr(WS.Name, “863”,”Notes”) > 0 Then
    WS.Visible = xlSheetVisible
    End If
    Next WS

    End Sub

  • 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

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

  • 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
    Sheet6.Activate
    Else
    ‘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
    Jan.Activate
    End If
    Application.ScreenUpdating = True
    End Sub

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

  • Jon,

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

  • 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!

    Jeff

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

  • 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

  • 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!

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

  • 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!

  • 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

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

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

  • 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

    • 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!

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly

Macros and VBA Training Webinar