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)

10

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

Understanding Functions in Macros

Functions are a common programming construct. They help you to create easy ways of processing information and returning a ...

Discover More

Managing Corporate Templates

Templates are used to store styles and lots of other customizations that affect how you use Word. On a single-user machine, ...

Discover More

Moving a Table Row

Want to move a row in a table very easily? You can do so by using the same editing techniques you are already using.

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Changing the Height of Worksheet Tabs

Do you need your worksheet tabs to be taller than what they are? You can't make the adjustment in Excel, but you can make it ...

Discover More

Moving from Sheet to Sheet

Need to move quickly through the worksheets in a workbook? Learn the keyboard shortcuts and you can make short work of this ...

Discover More

Returning a Worksheet Name

Need to know the name of the current worksheet? You can use the CELL function as the basis for finding this information and ...

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. 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 0 + 7?

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.