An Excel Macro to Hide All but the Active Worksheet - dummies

An Excel Macro to Hide All but the Active Worksheet

By Michael Alexander

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:

  1. Activate Visual Basic Editor by pressing Alt+F11.

  2. Right-click the project/workbook name in the project window.

  3. Choose Insert→Module.

  4. Type or paste the code in the newly created module.