How to Hide and Unhide Sheets in Excel with VBA (6 Examples)

Example 1 – Hide and Unhide a Single Sheet

We have a workbook that has five sheets, each containing an imaginary data table of “Records of Elementary School”. We want to hide one of them, then we will unhide the sheet again.

• To hide the “Elementary” sheet, copy the following code in a new Module and press the Run button.

Sub Hide_Single_sheet()
  Sheets("Elementary").Visible = xlSheetHidden
End Sub

• You can also use any of these lines to get the same output.

Worksheets("Elementary").Visible = False

• You can use a False statement instead of xlSheetHidden.

Sheets(1).Visible = False

Here, Sheets(1) takes the first sheet in the workbook.

This one line of code calls the “Elementary” sheet with the VBA Sheets function. Then, from the Visible properties, it selects the xlSheetHidden option.

• You can see we have hidden the “Elementary” sheet.

• Use the following code to unhide the “Elementary” sheet.

Sub Unhide_Single_Sheets()
    Sheets("Elementary").Visible = xlSheetVisible
End Sub

• You can also use any of the following lines.

Worksheets("Sheet1").Visible = True

• You can use the True statement instead of xlSheetVisible.

Sheets(1).Visible = True

We have the “Elementary” sheet back.

Example 2 – Using the xlSheetVeryHidden Property to Unhide

• Copy the following code into a new Module and click on the Run button to hide the sheet.

Sub Sheet_Very_Hidden()
    Worksheets("Elementary").Visible = xlSheetVeryHidden
End Sub

The “Elementary” sheet is invisible. And you cannot unhide the sheet manually.

• To unhide the sheet, use the following code.

Sub Unhide_Very_Hidden()
Sheets("Elementary").Visible = True
End Sub

If you run the code, the “Elementary” sheet will be visible.

Example 3 – Use of Array to Hide and Unhide Multiple Sheets

We want to hide three sheets: “Elementary”, “High School” and “College”.

• Copy the following code into a new Module and run it to hide multiple sheets using the following VBA code.

Sub Hide_Multiple_sheet_with_Array()
 Sheets_Arr = Array("Elementary", "High School", "College") 
    For i = LBound(Sheets_Arr) To UBound(Sheets_Arr)
     Sheets(Sheets_Arr(i)).Visible = False
    Next i
End Sub

After running the code, all those sheets have been hidden.

• To unhide those sheets, use the following code:

Sub Unhide_Multiple_sheet_with_Array()
 Sheets_Arr = Array("Elementary", "High School", "College")
    For i = LBound(Sheets_Arr) To UBound(Sheets_Arr)
     Sheets(Sheets_Arr(i)).Visible = True
    Next i
End Sub

After running the code, you can see all those hidden sheets have appeared.

Example 4 – Hide and Unhide Sheets by Tab Color

We changed the tab color of the “Middle School” and “College” sheets to blue. We want to hide and unhide these two sheets.

• Copy the following code into a new Module and press the Run button.

Sub Hide_Blue_Colored_Sheets()
 Dim ws As Worksheet
 Dim Tab_Color As Long
  Tab_Color = 12611584
 For Each ws In ActiveWorkbook.Sheets
    If ws.Tab.Color = Tab_Color Then
      ws.Visible = False
    End If
 Next ws
End Sub

This code checks the tab color of every worksheet to see if it is blue. Upon meeting the criteria, the code hides the corresponding sheet. The number 12611584 is the blue color code in VBA.

After running the code, you can see that those Blue colored tabs are not visible anymore.

• Use the following code to unhide the sheets.

Sub Unhide_Blue_Colored_Sheets()
Dim ws As Worksheet
 Dim Tab_Color As Long
   Tab_Color = 12611584
  For Each ws In ActiveWorkbook.Sheets
    If ws.Tab.Color = Tab_Color Then
     ws.Visible = True
    End If
  Next ws
 End Sub

After running the code, we can see those two sheets are visible now.

Example 5 – Hide and Unhide a Specific Worksheet Based on Cell Value in Another Sheet

We have these two sheets of “Sales” and “Summary”. In the “Sales” sheet there is a Hide/Unhide box. We will write Hide/Unhide in D15, and the “Summary” sheet will be hidden.

We have to use an Event Handler to perform this procedure. So, we can’t insert the code in any Module. We have to insert the code in the “Sales” sheet.

• Use the following code in the “Sales” sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
 If [D15] = "Hide" Then
  Sheets("Summary").Visible = False
  Else
  Sheets("Summary").Visible = True
 End If
End Sub

• Go to the “Sales” sheet and put “Hide” in the D15 cell.

The “Summary” sheet is no longer visible.

• To unhide the sheet, copy the following code into the same “Sales” sheet.

Private Sub Unhide_Worksheet_Change(ByVal Target As Range)
 If [D15] = "Unhide" Then
   Sheets("Summary").Visible = True
  Else
   Sheets("Summary").Visible = False
 End If
End Sub

• Go to the “Sales” sheet and put “Unhide” in D15, then press Enter.

The “Summary” sheet is restored.

Example 6 – Hide and Unhide Sheets in a Protected Workbook

• Use the following code to unprotect the workbook.

Sub Unprotect_Worksheet()
  ActiveWorkbook.Unprotect
 End Sub

• If your workbook is password-protected, use this code:

 Sub Unprotect_Worksheet()
 ThisWorkbook.Unprotect "password"
 End Sub

• Hide and unhide using any of the previous methods.

How to Unhide All Hidden Sheets with Excel VBA

We have already hidden some sheets. We will use a VBA code to unhide them.

• Use the following code in a new Module and press the Run button.

Sub Unhide_All_Sheets()
 Dim ws As Worksheet
  For Each ws In ThisWorkbook.Worksheets
   ws.Visible = True
 Next ws
End Sub

After running the code, you can see all the sheets are visible now.

How to Hide All Sheets Except for the Active Sheet

• Use the following code in a new Module and click on the Run button.

Sub Hide_All_but_Activesheet()
 Dim ws As Worksheet
   For Each ws In ThisWorkbook.Sheets
    If ActiveSheet.Name <> ws.Name Then
     ws.Visible = False
    End If
   Next ws
End Sub 

This code checks every worksheet in the workbook. If the worksheet name doesn’t match the Active Sheet, the worksheet will be hidden.

After running the code, you can only see the active sheet.

How to Hide All Sheets Except for One Sheet

We want to hide only the “Elementary” sheet.

• Use the following code.

Sub Hide_All_Sheets_Except_One()
 Dim ws As Worksheet
  For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Elementary" Then
     ws.Visible = False
    End If
  Next ws
End Sub

After running the code, all sheets have been hidden except for the “Elementary” sheet.

Frequently Asked Questions

How do we unhide all sheets?

Right-click on the Sheet tab at the bottom and select the “Unhide” option. In the Unhide dialog box, press the Ctrl key and click the sheets you want to show, or press the Shift + Up/Down Arrow keys to select multiple (or all) worksheets, and then press OK.

How do I automatically unhide sheets in Excel?

Right-click on a sheet tab, then click Select All Sheets on the shortcut menu. On the Home tab, click Format > Visibility > Hide & Unhide > Hide Sheet.

Things to Remember

• Make sure to call the sheet’s name properly. Check if there are any spaces or special characters in the sheet name. Also, you should be careful with capital letters.
• In the case of a protected sheet, you cannot hide or unhide any sheet without unprotecting it in the first place.
• If you try to hide a hidden sheet or unhide a visible sheet, the code could give you an error. For that, you can use Error Handler.
• It’s a good practice not to hide the Active Sheet. Doing so can give you unexpected results.
• You can’t hide all the sheets. At least one sheet always has to remain unhidden.

Download the Practice Workbook