Excel VBA to Hide Multiple Sheets (9 Suitable Ways)

In this article, we will discuss some examples of using Excel VBA to hide multiple Sheets. In a Workbook with multiple Sheets, we often don’t use each Sheet. For example, if we have separate Sheets to calculate and show results, then we need not show the calculation Sheets while presenting the Workbook to others. We can hide those Sheets that we do not want to show.

However, if we have Sheets with a significant number, hiding them manually is not only time-consuming but also monotonous. In this case, Excel VBA can help ease our tasks. We could easily hide multiple Sheets simultaneously by using Excel VBA codes. The following video shows how we can hide multiple Sheets using VBA codes in Excel.

Introduction to Worksheet.Visible Property in Excel VBA

In all cases, we have used the Worksheet.Visible property to hide multiple sheets in our Excel file. So, let’s familiarize ourselves with this property. This property mainly determines whether a worksheet is visible or not. The Visible property can have one of the following values:

• xlSheetVisible: The xlSheetVisible value is the default value for the Visible property, which means that the worksheet is visible by default.
• xlSheetHidden: If you want to hide a worksheet, you can set the Visible property to xlSheetHidden.
• xlSheetVeryHidden: The xlSheetVeryHidden value is similar to xlSheetHidden, but it is a stronger level of protection. If you set the Visible property to xlSheetVeryHidden, the worksheet will not be visible in the Excel user interface, and it cannot be unhidden using the regular methods. It can only be unhidden using VBA code.

Excel VBA Hide Multiple Sheets: 9 Suitable Examples

Today, we will talk about 9 examples that will teach you to hide multiple Sheets with VBA codes in Excel. Here, we have the employee, sales, revenue, and profit data of a company for a certain period. We will hide multiple Sheets at a time by applying various methods and conditions.

1. Hiding Multiple Sheets by Explicitly Mentioning Each Sheet Name in VBA

In this method, we will hide 4 of the 5 Sheets. We will mention each Sheet name that we want to hide in the VBA code. Then, we will make the .Visible property of being Sheets visible or hidden.

We will use the following VBA code to execute the task.

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

As we run the code by launching VBA Macro editor, the Sheets that we mentioned in the VBA code will be hidden.

2. Hide Multiple Sheets Using a List from Another Sheet

In this case, we will take the names of the Sheets that we want to hide from another Sheet.

Then, the following code will hide the Sheets from the list.

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

Here, the Sheets from the list are not visible.

3. Hiding Multiple Sheets Using For Loop

In this example, we will run a For Loop VBA through the list of Sheets that we want to hide and set the .Visible property to False and hide the Sheets in the process.

The following code will be used to complete the task.

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 a range comprising the B5:B8 cells of the For_Loop Sheet. Then, the code runs a For Loop and in each iteration, it sets the .Visible property of the Sheets from that list to xlSheetHidden.

As the code runs, only the Sheet that is not in the list will be visible.

4. Hiding Multiple Sheets with Specific Tab Color

In this instance, we have two Sheets with a specific tab color (light green in this case). We will hide the Sheets with specific tab colors.

We will use the code below to do the task.

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 of the Workbook and matches the tab color of each Sheet with the specific tab color which is light green in this case. If the tab color of a Sheet is light green, the code hides the Sheet.

As it is seen from the image, the Sheets having the light green tab color are hidden and the rest are visible.

5. Using Specific Value from Sheets to Hide Multiple Sheets

We can hide multiple Sheets by using a particular value present in the Sheets. In this case, some Sheets have the value Hide written in the B15 cell.

The following VBA code will hide those Sheets simultaneously.

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

This is a VBA  code that loops through all the Sheets using a For Loop in an Excel workbook and checks if the value in cell B15 of each Sheet is equal to “Hide”. If it is, then the code hides the Sheet.

As we can see, two of the five Sheets of our workbook contained the specific value and thus they are hidden.

6. Using Sheet Properties Tab to Hide Multiple Sheets

Here, we will not run a code rather we will go to the VBA Macro Editor window and from the property window of each Sheet and set the Visible property to xlSheetHidden.

Let’s follow the below steps to hide multiple sheets using Sheet properties.

• Click on any sheet name from the Microsoft Excel Object tab.
• Go to the Properties tab.
• From there, set the Visible property to xlSheetHidden.

Here, we set the Visible property of two Sheets to xlSheetHidden and hence they are hidden.

7. Hiding Multiple Sheets with xlSheetVeryHidden Option

Apart from xlSheetVisible and xlSheetHidden values, the .Visible property has another value which is xlSheetVeryHidden. This also hides the Sheet. However, in this case, we cannot unhide it by simply right-clicking on the sheet name. Because here, the Unhide command will be disabled. If a Sheet is very hidden then one has to unhide it from within the Visual Basic Editor.

We will execute the following code for the task.

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 out each Sheet that it will hide explicitly and set the .Visible property to xlSheetVeryHidden.

As the image below shows, only one Sheet is visible.

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

8. Hiding All Sheets Excluding ActiveSheet

In this method, we will hide all the Sheets of a Workbook except the ActiveSheet. Here, the ActiveSheet name is Active_Sheet. After running the following code only this Sheet will be visible.

We will use the following code to hide all the Sheets but the ActiveSheet.

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

As we can see, only the Active_Sheet is visible after running the code.

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

9. Hiding Multiple Sheets with Specific Name

Often we want to hide the Sheets with a particular name. For example, in this case, we have two Sheets (Sheet1 and Sheet2) that have no specific names. We will hide these two Sheets by using the following VBA code.

The following code will be used to hide the two Sheets.

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 uses a loop to iterate through all the Sheets in the Workbook and checks whether all the characters from the left of the Sheet’s name (excluding the last character) match the word “Sheet”. If they match, the Sheet is hidden. If they don’t match, the Sheet is made visible.

As we can see, all the Sheets whose names start with “Sheet” are hidden.

Hiding Multiple Sheets from Protected Workbook in Excel VBA

Users cannot hide a protected Workbook. The hide command is disabled as we right-click on the sheet name. The following code first unprotects a protected Workbook and then hides multiple Sheets from the Workbook.

The following code will unprotect the Workbook and then hide Sheets.

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

As we can see from the image below, the Sheets from the list are hidden. Also, now we can access the Hide command as the workbook is unprotected.

How to Hide and Unhide Sheets in Excel with VBA Button

Here, we will hide and unhide multiple Sheets with VBA Buttons. To accomplish this, do the following tasks.

• Go to Developer >> Insert.
• Select Button from the ActiveX Controls tab.
• Insert the Button in the Sheet.

• Here, we will insert two Buttons and rename them.
• Right-click on one of the Buttons and from the available options choose View Code.
• In the coding module, paste the following codes.

The following codes will be associated with the Command Buttons and hide-unhide Sheets.

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 too will run if we click on the Unhide Button. This code will unhide all the hidden Sheets.

As we can see from the video, if we click on the Hide Button, multiple Sheets will be hidden. If we click on the Unhide button, the Sheets will be visible again.

Read More: How to Hide and Unhide Excel Worksheets from a Workbook

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 laid out in a grid, where you can perform typical spreadsheet functions like calculations, data entry, and charting. It excludes chart sheets, macro sheets, and other types of sheets that don’t have cells in a grid. On the other hand, “Sheets()” refers to the collection of all sheets in the workbook, including worksheets as well as chart sheets, macro sheets, and any other type of sheet.

So, if you want to work specifically with worksheets that contain cells in a grid, you should use “Worksheets()”. If you want to work with all sheets in the workbook, including chart sheets and macro sheets, then use “Sheets()”.

• Can I hide sheets without the user seeing them briefly?

Yes, you can use the Application.ScreenUpdating property to prevent the user from seeing the sheets being hidden. Set the property to False before hiding the sheets and then set it back to True after the sheets are hidden. Here is an example code:

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

You can use the Workbook_Open event to automatically hide sheets when the workbook is opened. Here is an example code:

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

Download Practice Workbook

You can download the practice book here.

Conclusion

In this article, we came to know about Excel VBA to hide multiple Sheets. These used cases will allow users to understand the .Visible property of a Worksheet clearly and allow them to hide their Worksheets according to their needs.