Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Unhiding Multiple Worksheets.

Unhiding Multiple Worksheets

by Allen Wyatt
(last updated November 14, 2016)

11

When you choose to hide worksheets in a workbook, Excel allows you to hide multiple sheets with one action: all you need to do is select the sheets before actually doing the hiding. Unhiding worksheets is a different story, however. Excel only allows you to unhide one at a time. If you have many worksheets you want to unhide, this can be very tedious.

The only way around this is to use a macro to unhide the worksheets. The following VBA macro will unhide all the worksheets in the current workbook:

Sub UnhideAllSheets()
    Dim wsSheet As Worksheet

    For Each wsSheet In ActiveWorkbook.Worksheets
        wsSheet.Visible = xlSheetVisible
    Next wsSheet
End Sub

If you would rather not unhide all the worksheets at once, you can cause the macro to ask about each hidden worksheet and then unhide each that you agree to unhide. The following macro will handle this task:

Sub UnhideSomeSheets()
    Dim sSheetName As String
    Dim sMessage As String
    Dim Msgres As VbMsgBoxResult

    For Each wsSheet In ActiveWorkbook.Worksheets
        If wsSheet.Visible = xlSheetHidden Then
            sSheetName = wsSheet.Name
            sMessage = "Unhide the following sheet?" _
              & vbNewLine & sSheetName
            Msgres = MsgBox(sMessage, vbYesNo)
            If Msgres = vbYes Then wsSheet.Visible = xlSheetVisible
        End If
    Next wsSheet
End Sub

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9636) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Unhiding Multiple Worksheets.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Adding Data Labels to a Chart

Data labels can help identify data in a chart. Here's how to add data labels.

Discover More

Converting a Table into Text

Word includes a power table editor that allows you to create and work with tables easily. At some point, however, you might ...

Discover More

Running a Macro when a Worksheet is Activated

Want to run a macro when you first select a worksheet? You can do so by using one of the event handlers built into Excel, as ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Identifying the Last Cell Changed in a Worksheet

When someone changes a cell in a worksheet, Excel normally goes along its merry way of keeping everything up to date. It does ...

Discover More

Detecting Types of Sheets in VBA

When processing workbook information in a macro, you may need to step through each worksheet to make some sort of changes. It ...

Discover More

Creating a Worksheet Copy by Default

Excel makes it relatively easy to copy worksheets to a different workbook. That doesn't mean it couldn't be made simpler ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is four less than 9?

2017-08-30 21:13:36

Daitary Nayak

When I used to set the visible property of a worksheet to Xlsheetvisible it shows run time error unable to set visible property of worksheet class. 2 worksheets are hidden in the workbook I tried code on your blog as follows:
Sub UnHideMyGroup()
Sheets("Grade Term 1").Visible = True
Sheets("Grade Term 2").Visible = True
End Sub

but it shows compile error. Please guide me the steps to unhide 2 worksheets named as "Grade Term 1" and "Grade Term 2" in the workbook.


2016-11-14 14:03:05

Alec Whatmough

A Molloy:
I assume you want to unhide, say, 5 sheets out of a larger total of hidden sheets?
If so, you need to name them in the macro. eg:
Sub UnHideMyGroup()
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = True
Sheets("Sheet6").Visible = True
Sheets("Sheet12").Visible = True
Sheets("Sheet15").Visible = True
End Sub

You can do the same to re-hide them - just copy the macro, save with a new name and change the True statements to False.


2016-11-14 09:31:12

Col Delane

The second macro is missing the statement to declare the wsSheet variable (i.e. Dim wsSheet As Worksheet)


2016-06-28 11:04:10

Patel

Very Helpful and worked perfectly!
Thank you very much!


2016-06-20 11:01:11

A Molloy

Please advise on how I could unhide a group of worksheets, not one by one...
Thanks!


2014-05-08 10:35:36

Alan

I've realised what the problem was - I had the workbook protected! All works perfectly - great macros!


2014-05-08 10:30:17

Alan

I've tried copying and pasting both of these but when I try and run them I am getting a Run Time Error 1004 - Application-defined or object defined error. Any suggestions?

Thanks


2014-04-02 07:23:25

Bryan

Alex, see my last comment for a response to Mike's post... if you want to hide instead of unhide, change xlSheetHidden to xlSheetVisible, and vice versa...


2014-04-01 16:56:05

alex shaw

I too would like to know a respone to Mike Lewis last response as i'm also using this code and I would like to know how you could use this code in reverse. I would like to have a command button to allow users to select which tabs to hide.


2014-03-27 06:50:01

Bryan

@Mike: if you run your code line-by-line you'll quickly see that it's treating the "ElseIf" as a part of the "If wsSheet.Visible" if, NOT the "If Msgres = vbYes" if.

The problem is there are two ways to have an If statement: all on one line (without End If, no Else If or Else) and multi-line (must have End If, can have Else If or Else). So you need to make your second If statement multi-line and add an extra End If:

If Msgres = vbYes Then
wsSheet.Visible = xlSheetVisible
ElseIf Msgres = vbCancel Then
Exit Sub
End If


2014-03-27 03:53:22

Mike Lewis

Hi there
I've copied your code and it works really well but if I only want to unhide a couple of worksheets from a large numgber, it scrolls through the whole list before finishing. so I've modified the code slightly as shown below by changing vbYesNo to vbYesNoCancel and adding a line to exit if vbCancel is pressed. This seems to work ok except it takes a number of clicks on vbCancel before it operates. Is there a way to make the vbCancel button operate the first time it is clicked?

Also, it didn't work until I declared wsSheet As Worksheet.

Thank you

Mike Lewis

Sub UnhideSomeSheets()
Dim sSheetName As String
Dim sMessage As String
Dim Msgres As VbMsgBoxResult
Dim wsSheet As Worksheet

For Each wsSheet In ActiveWorkbook.Worksheets
If wsSheet.Visible = xlSheetHidden Then
sSheetName = wsSheet.Name
sMessage = "Unhide the following sheet?" _
& vbNewLine & sSheetName
Msgres = MsgBox(sMessage, vbYesNoCancel)
If Msgres = vbYes Then wsSheet.Visible = xlSheetVisible
ElseIf Msgres = vbCancel Then Exit Sub
End If

Next wsSheet

End Sub


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.