Sometimes, we have to show all of our hidden sheets in the sheet name bar. Using Excel VBA code, we can do such a task within the blink of an eye. In this article, we will demonstrate four examples of how to unhide all sheets using Excel VBA. If you are also interested in it, download our practice workbook and follow us.
Unhide All Sheets in Excel Using VBA: 4 Suitable Examples
We have a dataset of five employees of any company and their income for the first two months of January and February. Moreover, we have their total also. If you look at the sheet name bar, there will be only one sheet.
However, when we check inside the Unhide dialog box, we see there are five hidden worksheets. Now, we are going to unhide all of them using the VBA code in the following examples.
1. Applying VBA Code in Immediate Window
We will write a simple VBA code in the Immediate Window in this example. The steps to complete this example are given below:
📌 Steps:
- First of all, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press Alt+F11 to open the Visual Basic Editor.
- A dialog box will appear.
- Now, in the View tab, select the Immediate Window option. Besides that, you can press ‘Ctrl+G’ to display the Immediate Window.
- Then, write down the following visual code in the Immediate window.
For Each W_S In Sheets:W_S.Visible=True:Next- Finally, press Enter.
- You will see all five sheets are unhidden.
Thus, we can say that our VBA code worked perfectly, and we were able to unhide sheets using Excel VBA.
2. Using xlSheetVisible Property
In the following example, we are going to write a VBA code using the xlSheetVisible property to unhide all sheets. The steps of this process are given as follows:
📌 Steps:
- First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. You can also press Alt+F11 to open the Visual Basic editor.
- A dialog box will appear
- Now, in the Insert tab on that box, click on Module.
- After that, write down the following visual code in that empty editor box.
Sub Unhide_All_Sheets()
Dim W_S As Worksheet
For Each W_S In ActiveWorkbook.Worksheets
W_S.Visible = xlSheetVisible
Next W_S
End Sub- Close the Editor tab.
- Then, in the Developer tab, select the Macros option from the Code group.
- A new dialog box called Macro will appear. Select Unhide_All_Sheets.
- Finally, click on the Run button to run this code.
- You will notice that all five sheets will show in the sheet name bar.
So, we can say that our VBA code worked effectively, and we were able to unhide sheets using Excel VBA.
🔎 Breakdown of VBA Code
Sub Unhide_All_Sheets()👉 First, we provide a name for the sub-procedure which is Unhide_All_Sheets.
Dim W_S As Worksheet👉 Then, we declare a variable named W_S.
For Each W_S In ActiveWorkbook.Worksheets
W_S.Visible = xlSheetVisible
Next W_S👉 After that, we use a VBA For loop to make visible all the worksheets of our active workbook.
End Sub👉 Finally, we end the sub-procedure of this code.
3. Setting Worksheet Visibility to True
In this example, we will write a VBA code setting worksheet visibility to True to unhide all sheets. The steps of this approach are shown below:
📌 Steps:
- First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. You can also press Alt+F11 to open the Visual Basic Editor.
- A dialog box will appear
- After that, in the Insert tab on that box, click on Module.
- Next, write down the following visual code in that empty editor box.
Sub Unhide_Each_Sheets()
Dim W_S As Worksheet
For Each W_S In Worksheets
W_S.Visible = True
Next
End Sub- Finally, close the Editor tab.
- Now, in the Developer tab, select the Macros option from the Code group.
- As a result, a new dialog box called Macro will appear.
- Then, select Unhide_Each_Sheets and click on the Run button.
- You will see that within a second, all five sheets will appear.
Hence, we can say that our VBA code worked precisely, and we were able to unhide sheets using Excel VBA.
🔎 Breakdown of VBA Code
Sub Unhide_Each_Sheets()👉 At first, we provide a name for the sub-procedure which is Unhide_Each_Sheets.
Dim W_S As Worksheet👉 After that, we declare a variable named W_S.
For Each W_S In Worksheets
W_S.Visible = True
Next👉 Next, we use a VBA For Loop to make all the worksheets’ visibility status True.
End Sub👉 At last, we end the sub-procedure of this code.
4. Unhiding Sheets That Contain Specific Letter in Title
Finally, we are going to write a VBA code that will search for a specific letter in the title of all worksheets. If all your worksheets have a similar pattern of title, this code may help you to unhide all the sheets. The procedure is explained below step-by-step:
📌 Steps:
- Firstly, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press Alt+F11 to open the Visual Basic Editor.
- As a result, a dialog box will appear
- Next, in the Insert tab on that box, click on Module.
- Now, write down the following visual code in that empty editor box.
Sub Sheets_Contain_Same_Letter()
Dim W_S As Worksheet
For Each W_S In ActiveWorkbook.Worksheets
If InStr(W_S.Name, "H") > 0 Then
W_S.Visible = xlSheetVisible
End If
Next W_S
End Sub- Then, close the Editor tab.
- Afterward, in the Developer tab, select the Macros option from the Code group.
- A small dialog box called Macro will appear. Select Sheets_Contain_Same_Letter.
- Finally, click on the Run button.
- You will notice that all five sheets will appear in the sheet name bar.
At last, we can say that our VBA code worked successfully, and we were able to unhide sheets using Excel VBA.
💬 Things You Should Know
In this code, the letter or the string is case-sensitive. You have to input exactly the same letter or string. Otherwise, the code will not be able to unhide any sheet. For example, ‘h’ and ‘H’ have different identities here. If we write ‘h’ instead of ‘H’ in our code, we will not get any worksheet unhidden.
🔎 Breakdown of VBA Code
Sub Sheets_Contain_Same_Letter()👉 Firstly, we provide a name for the sub-procedure which is Sheets_Contain_Same_Letter.
Dim W_S As Worksheet👉 Next, we declare a variable named W_S.
For Each W_S In ActiveWorkbook.Worksheets
If InStr(W_S.Name, "H") > 0 Then
W_S.Visible = xlSheetVisible
End If
Next W_S👉 Then, we use a VBA For Loop to check every worksheet of our active workbook. Moreover, we apply a VBA For-Each Loop with the InStr function to check the availability of our desired text ‘H’ in our hidden sheet name. If the ‘H’ text remains in the sheet name, the code will unhide that sheet. Otherwise, the sheet remains hidden.
End Sub👉 In the end, we end the sub-procedure of this code.
Read More: How to Unhide Very Hidden Sheets in Excel
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and you will be able to unhide all sheets using Excel VBA. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.
