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”.
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.
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.
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.