Automatically hide and unhide all Excel sheets with this global macro

Developer tab

If you cannot see the Developer tab in the ribbon in Exel, here are the steps to make it visible.

Right click on the ribbon > Click Customize the Ribbon > On the far right hand pane, make sure the Developer checkbox is ticked > Click “OK”.

Personal workbook

In order to have a macro available to you every time you open up an Excel file, you need to have the macro saved in your Personal Workbook. The easiest way to do this is to create a dummy macro in your personal workbook via Developer tab (under the Code section) > Record Macro > Enter a random letter in the Shortcut key (I used the letter P) > Select Personal Workbook under Store macro in and then stop the recording via Developer tab > Stop recording.

Visual basic panel

We now need to edit this dummy macro to actually have the VBA code to hide and unhide all Excel sheets/tabs.

To do this open up the Visual Basic panel via Developer tab (under the Code section). You will notice some directories in the tree. Expand the directory called VBAProject (PERSONAL.XLSB), then expand the Modules directory, and then double click Module 1 to view the code.

VBA code

The VBA editor will display the code behind your recorded macro. We actually do not need any of this.

Replace all othe existing code with the below snippet:

Sub toggleSheetVis()
Dim i As Double
Dim j As Double
Dim k As Double
j = 0
For i = 1 To ActiveWorkbook.Worksheets.Count
    If i <> ActiveWorkbook.ActiveSheet.Index Then
        If ActiveWorkbook.Worksheets(i).Visible = True Then
            j = j + 1
        End If
        If j <> ActiveWorkbook.Worksheets.Count - 1 Then
            For k = 1 To ActiveWorkbook.Worksheets.Count
              If k <> ActiveWorkbook.ActiveSheet.Index Then
                ActiveWorkbook.Worksheets(k).Visible = True
                End If
            Next k
        Else
            For k = 1 To ActiveWorkbook.Worksheets.Count
              If k <> ActiveWorkbook.ActiveSheet.Index Then
                ActiveWorkbook.Worksheets(k).Visible = False
                End If
            Next k
        End If
    End If
Next i
End Sub

You can exit out of the Visual Basic panel by clicking the cross on the right and then exit the Excel file.

Toggle

Right click any blank area of the Ribbon/tab and then select Customize the Ribbon.

In the left pane, select Quick Access Toolbar and then under Choose commands from, select Macros in the dropdown. Now select PERSONAL.XLSB!toggleSheetVis and then click the Add>> button.

To take it a step further, we will customise the icon for this. With the macro still being selected (in the right hand side box), click the Modify button, select an icon, and then click the OK button. Now click the OK button here also.

Saving the global macro

Exit the Excel file by clicking the close icon on the top right and you will be given 2 prompts. Firstly, if you would like to save the current Excel file – do so if you need the current file. Second prompt will ask if you would like to Save the changes you made to the Personal Macro Workbook? – this is important – select Save.

You are done!

Now open up a new Excel file and provided you followed the above steps, you should see the toggle on the quick access on the top of the screen and when you click the icon, you should now be able to automatically hide and unhide all Excel sheets/tabs except the active sheet.