Excel Tip #12 - Unhide All Sheets From View At One Time

How to Unhide More Than One Sheet in Excel At A Time

If you work in auditing, finance, accounting, or work with a lot of modeling, you are constantly dealing with Excel workbooks that have multiple tabs or sheets. We are constantly changing our workbooks by adding more tabs or taking tabs away. Sometimes we don't want to delete a sheet because we fear that we will need it later, so we hide it. While nine out of ten times we end up not needing that tab, there is that one out of ten chance when we want to recall that tab we hid last week and also the other twenty we hid a month ago.

Microsoft Excel did not do a great job of building in a function to unhiding multiple tabs at once. In fact it is non-existent in Microsoft Excel 2010. One way we can unhide all twenty tabs is manually right-clicking on the tabs and select 'Unhide'. Alternatively you can right-click and then hit "U" to unhide. This is okay if we want to unhide one tab, but to have to do this twenty times can be cumbersome. Who has time to unhide all the sheets in a workbook one at a time?

Macro solution to unhiding all the tabs instantaneously
Refer to the below five step process:

1) Hit Alt + F11 (this will open the VBE Window)

2) From the 'Insert Menu' select 'Module' and paste the code below.

Sub UnhideAllSheets()
    Dim wks As Worksheet
    For Each wks In ActiveWorkbook.Worksheets
        If wks.Visible = False Then
            wks.Visible = xlSheetVisible
        End If
    Next wks            
End Sub

3) Select 'References' from the 'Tool Menu' and make sure 'Microsoft Forms 2.0 Object Library' is selected. If it's not listed then click 'browse' and select 'Fm20.dll'

4) Close the VBE window

5) Hit Alt + F8, select 'UnhideAllSheets', click 'Options' and set your shortcut key.

Labels: ,