Using Excel VBA to Hide Multiple Sheets – 9 Methods

The Worksheet.Visible Property in Excel VBA

The Worksheet.Visible property hides multiple sheets in an Excel file. The Visible property can have one of the following values:

• xlSheetVisible: The xlSheetVisible value is the default value: the worksheet is visible by default.
• xlSheetHidden: Hides a worksheet.
• xlSheetVeryHidden: It is similar to the xlSheetHidden, but has a stronger level of protection. The worksheet will not be visible in the Excel user interface, and can only be unhidden using a VBA code.

Method 1 – Hiding Multiple Sheets by Mentioning Each Sheet Name in the VBA code

Hide 4 of the 5 Sheets:

• Use the following code.

Sub ExplicitNameMention()
'declaring variables
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
Set ws1 = Worksheets("Sales")
Set ws2 = Worksheets("Revenue")
Set ws3 = Worksheets("Profit")
Set ws4 = Worksheets("Sheet_Names")
'hide four worksheets at the same time
ws1.Visible = xlSheetHidden
ws2.Visible = xlSheetHidden
ws3.Visible = xlSheetHidden
ws4.Visible = xlSheetHidden
End Sub

The sheets will be hidden.

Method 2 – Hide Multiple Sheets Using a List from Another Sheet

Use the names of the sheets to hide:

• Use the following code.

Sub List_From_Another_Sheet()
'setting variable values
Set ws1 = Worksheets("List_From_Another_Sheet").Range("B5")
Set ws2 = Worksheets("List_From_Another_Sheet").Range("B6")
Set ws3 = Worksheets("List_From_Another_Sheet").Range("B7")
Set ws4 = Worksheets("List_From_Another_Sheet").Range("B8")
'hide four worksheets at the same time
Worksheets(ws1.Value).Visible = xlSheetHidden
Worksheets(ws2.Value).Visible = xlSheetHidden
Worksheets(ws3.Value).Visible = xlSheetHidden
Worksheets(ws4.Value).Visible = xlSheetHidden
End Sub

The sheets in the list are not visible.

Method 3 – Hiding Multiple Sheets Using a For Loop

• Use the following code.

Sub Using_For_Loop()
'declaring variable
Dim ws As Worksheet
'setting value of the variable
Set Rng = Worksheets("For_Loop").Range("B5:B8")
'running for loop
For Each Cell In Rng
    Worksheets(Cell.Value).Visible = True
Next Cell
End Sub

The above code declares B5:B8 of the For_Loop Sheet and in each iteration, it sets the .Visible property of the Sheets in the list to xlSheetHidden.

Only the Sheet that is not in the list will be visible.

Method 4 – Hiding Multiple Sheets with a Specific Tab Color

• Use the following code.

Sub Tab_Color()
'declaring variables
Dim tabcolor As Long
Dim ws As Worksheet
'setting the numeric value of the light green color as the_
'_tabcolor value
tabcolor = 5296274
'running for loop to hide the sheets with light green tabcolor
For Each ws In ActiveWorkbook.Sheets
    If ws.Tab.Color = tabcolor Then
        ws.Visible = xlSheetHidden
    End If
Next ws
End Sub

The above code runs a For Loop through the worksheets in the workbook and matches their tab color with a specific tab color (light green). If the tab color is light green, the code hides the sheet.

Sheets with a light green tab are hidden.

Method 5 – Using a Specific Value to Hide Multiple Sheets

• Use the following code.

Sub Specific_Value()
'declaring variable
Dim ws As Worksheet
'running For Loop through each sheet
For Each ws In ActiveWorkbook.Sheets
    If ws.Range("B15").Value = "Hide" Then
        ws.Visible = xlSheetHidden
    End If
Next ws
End Sub

A For Loop iterates through the sheets and checks if the value in B15 is equal to “Hide”. If it is, the code hides the sheet.

Two of the five sheets in the workbook are hidden.

Method 6 – Using the Sheet Properties Tab to Hide Multiple Sheets

• Click any sheet name in the Microsoft Excel Object tab.
• Go to the Properties tab.
• Set Visible to xlSheetHidden.

Two sheets set to xlSheetHidden are hidden.

Method 7 – Hiding Multiple Sheets using the xlSheetVeryHidden Option

• Use the following code.

Sub Very_Hidden()
'declaring variables
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
'setting variable values
Set ws1 = Worksheets("Sales")
Set ws2 = Worksheets("Sheet_Name")
Set ws3 = Worksheets("Revenue")
Set ws4 = Worksheets("Profit")
'hiding 4 sheets at a time
ws1.Visible = xlSheetVeryHidden
ws2.Visible = xlSheetVeryHidden
ws3.Visible = xlSheetVeryHidden
ws4.Visible = xlSheetVeryHidden
End Sub

The code calls each sheet and hides it, setting the .Visible property to xlSheetVeryHidden.

Only one sheet is visible.

Read More: How to Unhide All Sheets in Excel Using VBA

Method 8 – Hiding All Sheets Excluding the Active Sheet

• Use the following code.

Sub Except_ActiveSheet()
'declaring variable
Dim ws As Worksheet
'running For Loop to hide sheets other than the ActiveSheet
For Each ws In ActiveWorkbook.Sheets
    If ws.Name = ActiveSheet.Name Then
        ws.Visible = xlSheetVisible
    Else
        ws.Visible = xlSheetHidden
    End If
Next ws
End Sub

This is the output.

Read More: VBA to Hide All sheets Except One in Excel

Method 9 – Hiding Multiple Sheets with a Specific Name

To hide Sheet1 and Sheet2:

• Use the following code.

Sub Specific_Name()
'declaring variable
Dim ws As Worksheet
'running For Loop to hide sheet with specific name
For Each ws In ActiveWorkbook.Sheets
    If Left(ws.Name, Len(ws.Name) - 1) = "Sheet" Then
        ws.Visible = xlSheetHidden
    Else
        ws.Visible = xlSheetVisible
    End If
Next ws
End Sub

The code iterates through all the sheets and checks whether the characters starting from the left in the name of the sheet (excluding the last character) match the word “sheet”. If they do, the sheet is hidden.

This is the output.

Hiding Multiple Sheets from a Protected Workbook in Excel VBA

A protected workbook cannot be unhidden. The hide command is disabled if you right-click the sheet name.

• Use the code:

Sub Protected_WorkBook()
'unprotecting workbook with password 123
ThisWorkbook.Unprotect ("123")
Dim ws As Worksheet
Set Rng = Worksheets("Sheet_Name").Range("B5:B7")
'running For Loop to hide the Sheets
For Each Cell In Rng
    Worksheets(Cell.Value).Visible = False
Next Cell
End Sub

Sheets in the list are hidden. The Hide command is enabled.

How to Hide and Unhide Sheets in Excel with a VBA Button

• Go to Developer >> Insert.
• Select Button in ActiveX Controls.
• Insert the Button in the sheet.

• Here, two Buttons will be inserted and renamed.
• Right-click one of the Buttons and choose View Code.
• In the code module, use the following codes.

Private Sub CommandButton1_Click()
Set ws1 = Worksheets("Sheet_Name").Range("B5")
Set ws2 = Worksheets("Sheet_Name").Range("B6")
Set ws3 = Worksheets("Sheet_Name").Range("B7")
Set ws4 = Worksheets("Sheet_Name").Range("B8")
'hide four worksheets at the same time
Worksheets(ws1.Value).Visible = xlSheetHidden
Worksheets(ws2.Value).Visible = xlSheetHidden
Worksheets(ws3.Value).Visible = xlSheetHidden
Worksheets(ws4.Value).Visible = xlSheetHidden
End Sub

Private Sub CommandButton2_Click()
Set ws1 = Worksheets("Sheet_Name").Range("B5")
Set ws2 = Worksheets("Sheet_Name").Range("B6")
Set ws3 = Worksheets("Sheet_Name").Range("B7")
Set ws4 = Worksheets("Sheet_Name").Range("B8")
'hide three worksheets at the same time
Worksheets(ws1.Value).Visible = xlSheetVisible
Worksheets(ws2.Value).Visible = xlSheetVisible
Worksheets(ws3.Value).Visible = xlSheetVisible
Worksheets(ws4.Value).Visible = xlSheetVisible
End Sub

The Private Sub CommandButton2_Click() is also an Event. The Subroutine will run if you click the Unhide Button. It will unhide all hidden Sheets.

Frequently Asked Questions

• What is the difference between Sheets () and Worksheets () in VBA?

In VBA (Visual Basic for Applications), both “Sheets()” and “Worksheets()” refer to the collection of worksheets in a workbook in Excel. However, there is a subtle difference between the two.

“Worksheets()” refers specifically to the collection of worksheets that contain cells in a grid. It excludes chart sheets, macro sheets, and other types of sheets that don’t have cells in a grid.

“Sheets()” refers to the collection of all sheets in the workbook, including worksheets, chart sheets, macro sheets, and any other type of sheet.

• Can I hide sheets and prevent other users from seeing them?

Yes, use the Application.ScreenUpdating property to prevent users from seeing the hidden sheets. Set the property to False before hiding the sheets and then set it back to True. The code below is an example:

Sub HideSheetsWithoutUserSeeing()
Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Visible = xlSheetHidden
Next ws
Application.ScreenUpdating = True
End Sub

• How can I hide sheets when the workbook is open?

Use the Workbook_Open event to automatically hide sheets. The code below is an example:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Visible = xlSheetHidden
Next ws
End Sub

Download Practice Workbook

Download the practice book here.