How to Automatically Hide Specific Worksheets When Opening an Excel File?

In Excel, you may want to auto-hide a specific sheet but do not want to delete it because it contains sensitive information that you do not want to share. We can accomplish this by utilising the VBA application. This article will help you understand how we can automatically hide specific worksheets when opening an Excel file. This tutorial will help you understand how you can automatically hide a single sheet and multiple sheets in Excel.

Automatically Hide a Single Worksheet

Here we will apply the VBA code to the whole workbook, and we will mention the sheet name in code. Let us see a straightforward process to see how we can automatically hide a single worksheet when opening an Excel file.

Step 1

Let us consider an Excel workbook that contains multiple worksheets. Right-click on any sheet name and select View Code to open the VBA application. then double-click on this workbook and type the programme into the textbox as shown in the below image.

Example

Private Sub Workbook_Open()
'Updated By Nirmal
Sheets("Sheet3").Visible = False
End Sub

In the code, "Shtee3” is the name of the sheet we are hiding.

Step 2

Now, save the sheet as a macro-enabled workbook and close it. The next time you open it, click on the enable code button to automatically hide Sheet 3.

Automatically Hide Multiple Worksheets

The sheet name will be grouped here and mentioned in the code. Let us see an effortless process to see how we can automatically hide multiple worksheets in an Excel workbook.

Step 1

Let us consider an Excel workbook that contains multiple worksheets. In any worksheet, enter the list of sheet names you want to hide, select them, and name them, as shown in the image below.

Step 2

Now right-click on any sheet name and select View Code to open the VBA application, and double-click on this workbook and type the programme into the textbox as shown in the below image.

Example

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Update By Nimral
Dim ws As Worksheet
For Each ws In Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub
Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
If WorksheetFunction.CountIf([Hidesheets], ws.Name) > 0 Then
ws.Visible = xlSheetHidden
MsgBox ws.Name & "Has been hidden!", vbInformation, "Kutools for Excel"
Else
ws.Visible = xlSheetVisible
End If
Next ws
Set ws = Nothing
End Sub

Step 3

Close the sheet, save it as a macro-enabled workbook, and then close it. The next time you open the sheet, click on the enable code button to automatically hide it, as shown in the image below.

Conclusion

In this tutorial, we used a simple example to demonstrate how we can automatically hide multiple worksheets in an Excel workbook.