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
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
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