Article 28 – How to Unhide all Tabs (Worksheets)

If you have many hidden worksheets and you want to hide them in one go, Excel doesn't provide an options to do so. There are many ways to to accomplish the same.

Option 1 – Use Custom Views

1. Unhide all of your sheets (you may use Option 2 or Option 3 below for the same also)
2. Views tab > Custom Views > Add and give this view a name (for example "ShowAll")

Now, whenever you want to Unhide all your sheets, simply select this view from Custom Views.  (You can create many views and can play with these to have many different kind of views)

Option 2 – Use one line of VBA code

1. ALT+F11
2. If Immediate Window is not displayed, CTRL+G
3. Copy and paste below code in Immediate Window and hit enter

For i=1 to Worksheets.Count:Worksheets(i).Visible = True: Next

Option 3 – Use a Macro

1. ALT+F11
2. Locate your Workbook name in Project Explorer Window
3. Right click on your workbook name > Insert > Module
4. Copy paste the below code in this
5. ALT+F8 to display Macro Window
6. Run your Macro from here

Sub UnhideAllSheets()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = 1 To Worksheets.Count
        Worksheets(i).Visible = True
    Next i
    Application.ScreenUpdating = True
End Sub

Option 4 – This Macro will display the dialog box which sheets we want to display one by one and depending upon Yes / No, it will unhide them. (Install instructions as in Option 2)

Sub UnhideAllSheetsOneByOne()
    Dim i As Long
    Dim Answer As String
    For i = 1 To Worksheets.Count
        If Worksheets(i).Visible = False Then
            Answer = MsgBox("Do you want to Unhide " & Worksheets(i).Name, vbQuestion + vbYesNoCancel, "Unhide Sheets")
            Select Case True
                Case Answer = vbYes: Worksheets(i).Visible = True
                Case Answer = vbCancel: Exit Sub
            End Select
        End If
    Next i
End Sub