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 this sheet?" & _
                         vbNewLine & wsSheet.Name
            msgResult = MsgBox(strMessage, vbYesNo)
            If msgResult = vbYes Then
                wsSheet.Visible = xlSheetVisible
            End If
        End If
    Next wsSheet
End Sub