Hide Unhide Single or Multiple Excel Sheets by VBA

This article will help you know how to hide and unhide excel sheets by VBA macro. Hide or unhide VBA code is very helpful when your excel workbook contains many worksheets and you want to show only specific worksheets to user. You can use VBA code to hide or unhide single or multiple sheets.

VBA Code to Hide a Single Sheet

We use Visible property of worksheet to hide a sheet using VBA. Visible property is set either to False or xlSheetHidden.

Suppose that you want to hide “Sheet3”, you can use any of below given VBA syntax to hide the sheet.

Sheets("Sheet3").Visible = False

or

Sheets("Sheet3").Visible = xlSheetHidden

VBA Code to Unhide a Single Sheet

To unhide a Sheet using VBA, set Visible property either to True or xlSheetVisible.

Suppose that you want to unhide “Sheet3”, you can use any of below given VBA code to unhide the sheet.

Sheets("Sheet3").Visible = True

or

Sheets("Sheet3").Visible = xlSheetVisible

VBA Macro to Hide Many Worksheets

In Excel workbook, all worksheets can not be hidden. One worksheet must always be visible. So if you want to hide all the sheets except one sheet, you can use For Each Loop and set Visible property of each sheet to False except a specific sheet.

Below given VBA procedure will hide all worksheets except one sheet with name “Order Details”.

Sub hide_sheet_VBA()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets

    If ws.Name <> "Order Details" Then

        ws.Visible = False

    End If

Next ws

End Sub

VBA Macro to Unhide All Hidden Worksheets

If in a excel workbook, many worksheets are hidden and you want to unhide all those hidden worksheets in one go, the below given VBA procedure will help you to that.

Sub Unhide_sheet_VBA()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
    
    ws.Visible = True

Next ws

End Sub

Leave a Reply