Microsoft Excel Tip – Unhiding Multiple Worksheets

This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.

Excel allows you to only unhide a single worksheet at a time.  The way around this is to use a macro that allows you to unhide lots of worksheets at once.  Save the following macros to Personal.xlsb to make them available to all Excel 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.Workheets
        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 strMessage As String
    Dim msgResult As VbMsgBoxResult
    Dim wsSheet As Worksheet
    For Each wsSheet In ActiveWorkbook.Worksheets
        If wsSheet.Visible = xlSheetHidden Then
            strMessage = "Unhide the following sheet?" & _
                         vbNewLine & wsSheet.Name
            msgResult = MsgBox(strMessage, vbYesNoCancel)
            If msgResult = vbYes Then
                wsSheet.Visible = xlSheetVisible
            End If
            If msgResult = vbCancel Then
                Exit For
            End If
        End If
    Next wsSheet
End Sub
Advertisements
Published in: on May 12, 2009 at 2:46 am  Leave a Comment  

The URI to TrackBack this entry is: https://blackbeltreview.wordpress.com/2009/05/12/excel-tip-unhiding-multiple-worksheets/trackback/

RSS feed for comments on this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: