How to hide all sheets except the active sheet using VBA?

To hide all sheets except the active sheet in an Excel workbook using VBA, you can use the following macro. This code loops through all the sheets in the workbook and hides every sheet except the one that’s currently active.

Here’s the VBA code you can use:

Sub HideAllExceptActive()
    Dim ws As Worksheet
    Dim activeSheet As Worksheet
    
    ' Set the active sheet to a variable
    Set activeSheet = ActiveSheet
    
    ' Loop through all worksheets in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Check if the worksheet is not the active sheet
        If ws.Name <> activeSheet.Name Then
            ' Hide the sheet
            ws.Visible = xlSheetHidden
        End If
    Next ws
End Sub

How to Use the VBA Code:

Press `ALT` + `F11` to open the Visual Basic for Applications editor.
Go to `Insert` > `Module` to create a new module.
Copy and paste the above code into the module window.
Close the VBA editor.
Run the macro by pressing `ALT` + `F8`, selecting `HideAllExceptActive`, and clicking `Run`.

This script checks each worksheet in the workbook and hides it if its name does not match the name of the active sheet. Be aware that Excel has different levels of visibility for sheets, including `xlSheetVisible` (visible), `xlSheetHidden` (hidden but unprotected), and `xlSheetVeryHidden` (hidden and can only be made visible again through VBA). This macro uses `xlSheetHidden`, making the sheets easily accessible for making them visible again if necessary.

facebooklinkedin