Excel VBA to Hide Multiple Sheets (9 Suitable Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Five Worksheets visible

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

excel vba Code to hide multiple sheets

VBA Code Breakdown

In this code,

  • First,declare 4 variables ws1, ws2, ws3, and ws4 as the Worksheet object.
  • Set the values of those Worksheet variables to 4 Sheets that we want to hide.
  • Finally, make the .Visible property to xlSheetHidden.

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

Only one Worksheet visible after running the Code Excel VBA hide multiple Sheets


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.

Five Worksheets  visible before running the Code

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

Code to hide multiple Sheets from list

VBA Code Breakdown

In the current VBA code,

  • We assign the Worksheets whose names are present in the B5:B8 range of the List_From_Another_Sheet as the value of the 4 Worksheet variables.
  • Then, set the .Visible property of each Sheet from the list to xlSheetHidden.

Here, the Sheets from the list are not visible.

One Worksheet 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.

Five Worksheets visible before executing For Loop

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

Code to run For Loop to hide Sheets

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.

One Worksheet visible after running For Loop


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.

Two Worksheets with specific color

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

Code to hide Worksheets with specific tab color

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.

Worksheets with specific tab color are hidden


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.

Worksheet containing a specific value

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

VBA Code to hide Sheets with a specific value

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.

Sheets with specific value 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.

Visible Worksheets before changing Visible property

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.

Changing Visible property

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

Three Sheets visible after changing Visible property


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.

All the visible Sheets before running the Code

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

Code to apply VeryHidden property to hide Sheets

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.

One Sheet visible after running the Code

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.

Four Sheets visible along with the ActiveSheet

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

Code to hide all the Sheets except ActiveSheet

VBA Code Breakdown

The code uses a loop to iterate through all the Sheets in the Workbook and checks whether the Sheet’s name is the same as the ActiveSheet’s name. If the Sheet’s name matches the ActiveSheet’s name, the Sheet is made visible. If the Sheet’s name doesn’t match the ActiveSheet’s name, the Sheet is hidden.

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

Only ActiveSheet visible

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.

Sheets with specific name visible

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

VBA Code to hide Sheets with specific name

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.

Sheets with specific name 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.

Hiding Sheets from protected 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

VBA Code to hide Sheets from protected Workbook

VBA Code Breakdown

This is a VBA code that is used to unprotect a Workbook with a password and then hide specific Sheets based on the values in a range. The code first unprotects the Workbook using the Unprotect method with the password “123”. Then, it declares a variable named “ws” as a Worksheet. Next, the code sets a range named “Rng” to refer to cells B5 to B7 on a Sheet named “Sheet_Name”. This range is used to determine which Sheets will be hidden.

Inside the For Loop, the code iterates through each cell in the range referred to by “Rng”. For each cell, the code hides the Sheet whose name matches the value in the cell.

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.

Sheets from protected Workbook are hidden


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.

Inserting Command Button

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

Accessing Command Button 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

Code to hide and unhide Sheets using Command Button

VBA Code Breakdown

Here, Private Sub CommandButton1_Click() is an Event. When we click on the Hide Button the code under this Subroutine will run. The code takes the Sheet names from the B5:B8 range and assigns them to  Worksheet variables. Then, it sets the .Visible property of each Sheet to xlSheetHidden.

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.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Adnan Masruf
Adnan Masruf

Adnan Masruf, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. His deep passion for research and innovation seamlessly aligns with his dedication to Excel. In this capacity, Masruf not only skillfully addresses challenging issues but also exhibits enthusiasm and expertise in gracefully navigating intricate situations, underscoring his steadfast commitment to consistently delivering exceptional content. His interests... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo