Macro 18: Hide All but the Active Worksheet
You may not want to delete all but the active sheet as we did in the last macro. Instead, a more gentle
option is to simply hide the sheets. Excel doesn’t let you hide all sheets in a workbook — at least one has
to be showing. However, you can hide all but the active sheet.
How it works
This macro loops the worksheets and matches each worksheet name to the active sheet’s name. Each
time the macro loops, it hides any unmatched worksheet.
Sub Macro18()
‘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
1. Step 1 declares an object called ws. This creates a memory container for each worksheet the
macro loops through.
2. Step 2 begins the looping, telling Excel to evaluate all worksheets in this workbook. There is
a difference between ThisWorkbook and ActiveWorkbook. The ThisWorkbook
object refers to the workbook that the code is contained in. The ActiveWorkbook object
refers to the workbook that is currently active. 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, we don’t want to risk hiding sheets in other workbooks, so we use ThisWorkbook.
3. In this step, the macro simply compares the active sheet name to the sheet that is currently
being looped.
4. If the sheet names are different, the macro hides the sheet.
5. In Step 5, we loop back to get the next sheet. After all of the sheets are evaluated, the macro
ends.
if they right-click on any tab and choose Unhide. The only way to unhide a sheet hidden in this
manner is by using VBA.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the 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.
