An Excel Macro to Hide All but the Active Worksheet
You may want to hide the inactive worksheets with this macro. Excel doesn’t let you hide all sheets in a workbook; at least one has to be displayed. However, you can hide all but the active sheet.
How the macro works
The macro here loops through the worksheets and matches each worksheet name to the active sheet’s name. Each time the macro loops, it hides any unmatched worksheet.
Sub Macro1() 'Step 1: Declare your variables Dim ws As Worksheet 'Step 2: Start looping through all worksheets For Each ws In ThisWorkbook.Worksheets 'Step 3: Check each worksheet name If ws.Name <> ThisWorkbook.ActiveSheet.Name Then 'Step 4: Hide the sheet ws.Visible = xlSheetHidden End If 'Step 5: Loop to next worksheet Next ws End Sub
Step 1 declares an object called ws. This step creates a memory container for each worksheet that the macro loops through.
Step 2 begins the looping, telling Excel to evaluate all worksheets in this workbook. Note the difference between ThisWorkbook and ActiveWorkbook. The ThisWorkBook object refers to the workbook that contains the code. The ActiveWorkBook object refers to the currently active workbook. They often return the same object, but if the workbook running the code is not the active workbook, they return different objects. In this case, you don’t want to risk hiding sheets in other workbooks, so you use ThisWorkBook.
In Step 3, the macro simply compares the active sheet name to the sheet that is currently being looped.
If the sheet names are different, the macro hides the sheet in Step 4.
In Step 5, you loop back to get the next sheet. After all sheets are evaluated, the macro ends.
Note that you use xlsheetHidden in your macro. This property applies the default hide state you would normally get when you right-click a sheet and select Hide. In this default hide state, a user can right-click any tab and choose Unhide, which displays all hidden sheets. But another hide state is more clandestine than the default. If you use xlSheetVeryHidden to hide your sheets, users will not be able to see them at all — even if they right-click a tab and choose Unhide. The only way to unhide a sheet hidden in this manner is to use VBA.
How to use the macro
To implement this macro, you can copy and paste it into a standard module:
Activate Visual Basic Editor by pressing Alt+F11.
Right-click the project/workbook name in the project window.
Type or paste the code in the newly created module.