Sometimes, we might wonder to hide the secret or unnecessary sheets in terms of working with Excel. If you are looking for a better way to do it, you are welcome. You have come to the right place. In this article, I will try to explain 2 smart ways the topic of using Excel VBA to Hide All Sheets Except One. I hope it will be helpful for you.
How to Launch VBA Editor in Excel
We often create a module in order to work with VBA. First of all, we need to go to the Developer tab and select Visual Basic to create a module.
Once you have opened the editor, then click on the Insert tab and pick the Module option.
A module will be created and we can write the required code in that module to execute the program.
Difference Between Hidden Sheets and Very Hidden Sheets in Excel
We can keep a selected sheet visible and the rest are hidden in Excel with VBA. Again, we can do it in 2 different ways. They are called Hidden and Very Hidden. They both hide the all other sheets except one. But there are some major differences between them. They are discussed in the following section.
The basic difference between Hidden Sheets and Very Hidden Sheets in Excel is that Hidden means the sheets are visible in the workbook but not as a sheet tab. But the Very Hidden means the sheets are hidden from the workbook and sheet tab.
Again, Very Hidden sheets can only be recovered with VBA. No other options are available. But Hidden sheets can be made visible again with VBA as well as clicking on the Unhide option after right-clicking on the mouse keeping the cursor on the visible sheet tab.
VBA Hide All Sheets Except One: 2 Smart Ways
There are 2 very smart ways to hide all sheets except one with VBA in Excel.
- Normal hidden
- Special type of hidden.
They are described briefly in the following sections.
To make it clearer to understand, I have created a workbook with three sheets naming the sheets as Exchange Rate-2021, Exchange Rate-2020, and Exchange Rate-2019.
1. Hide All Sheets Except One with xlSheetHidden Statement
In order to hide all sheets except one in Excel, we can use the xlSheetHidden command under the Visible property. As we want to keep one sheet and delete all others, we need to run a condition to keep the active one intact and the rest hidden.
Sub HideAllSheets_ExceptOne()
Dim WS As Worksheet
Dim Preserve_Sheet As String
'Define the Sheet name that you want you keep
Preserve_Sheet = ActiveSheet.Name
'Loop through all the sheets
For Each WS In ActiveWorkbook.Sheets
'Apply condition to hide all sheets except one
If WS.Name <> Preserve_Sheet Then
WS.Visible = xlSheetHidden
End If
Next WS
End SubCode Breakdown
Under the HideAllSheets_ExceptOne sub-procedure, I have defined the active sheet name as Preserve_Sheet which I want to keep.
Then, I looped through each sheet and hid all other sheets with the xlSheetHidden command under the Visible property and ran a condition to keep the Preserve_Sheet.
2. Hide All Sheets Except One with xlSheetVeryHidden Statement
There is another type of hide option in VBA which is called Very Hidden. In this process, we will use the xlSheetVeryHidden command under the Visible property. The main feature of this hidden type is that the sheets are hidden from both the workbook and sheet tab. It can only be returned with a VBA code. No other way to get back the hidden sheets at all.
Sub VeryHiddenAllSheets_ExceptOne()
Dim WS As Worksheet
Dim Preserve_Sheet As String
'Define the Sheet name that you want you keep
Preserve_Sheet = "Exchange Rate-2021"
'Loop through all the sheets
For Each WS In ActiveWorkbook.Sheets
'Apply condition to hide all sheets except one
If WS.Name <> Preserve_Sheet Then
WS.Visible = xlSheetVeryHidden
End If
Next WS
End SubCode Breakdown
Here, I have defined the active sheet name as Preserve_Sheet which I want to keep Under the VeryHiddenAllSheets_ExceptOne sub procedure.
Then, I looped through each sheet and hid all other sheets with the xlSheetVeryHidden command under the Visible property and ran a condition to keep the Preserve_Sheet.
Read More: Excel VBA to Hide Multiple Sheets
VBA to Unhide Hidden Sheets in Excel
It is not that the hidden sheets can never be got back. It is possible. You can use a simple VBA code to unhide hidden sheets in Excel easily.
Just a reminder, the hidden sheets can’t be got back using the “Unhide” command from the context menu with this process if the sheets are Very Hidden. In fact, the Unhide option will be unavailable.
In this case, you need to use the xlSheetVisible command under the Visible property. Once you run the code, the hidden sheets as well as the very hidden sheets will be restored.
Frequently Asked Questions
- How to Protect Sheet with VBA?
We can protect a sheet from doing any kind of further editing. We can use a password as protection. You just need to use the following VBA command.
Worksheets(“Worksheet_Name”).Protect Password:="Set_Password"- Is VBA applicable in hidden sheets?
No, VBA won’t apply to the sheets that are already hidden. For this, you have to unhide the worksheets and then apply the VBA.
To unhide the hidden sheets, use the following VBA code.
Worksheet.Visible = xlSheetVisibleAfter unhiding the hidden sheets, you can apply your defined VBA code.
- How to hide all sheets in an Excel workbook except for a specific sheet name?
In order to hide all sheets in an Excel workbook except for a specific sheet name, we can apply a condition inside a For Loop like the following VBA code.
For each Worksheet in ThisWorkbook.Worksheets
If Worksheet.Name = “Preferred_Name” then
Worksheet.Visible = xlSheetHidden
End If
NextDownload Practice Workbook
You can download the practice workbook from here.
Conclusion
In the above section, I have discussed 2 smart ways on the topic of VBA to Hide All Sheets Except One in Excel as well as to restore the hidden sheets. Considering the characteristics of those types, you can choose any one of them based on your preferences. I hope this article will be very helpful for you.
