How to Unhide All Sheets in Excel Using VBA (4 Examples)

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.


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


4 Suitable Examples to Unhide All Sheets in Excel Using VBA

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.

Embedding VBA Code

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

Applying VBA Code in Immediate Window to Unhide All Sheets Using Excel VBA

  • Then, write down the following visual code in the Immediate window.

Applying VBA Code in Immediate Window to Unhide All Sheets Using Excel VBA

For Each W_S In Sheets:W_S.Visible=True:Next
  • Finally, press Enter.
  • You will see all five sheets are unhidden.

Applying VBA Code in Immediate Window to Unhide All Sheets Using Excel VBA

Thus, we can say that our VBA code worked perfectly, and we were able to unhide sheets using Excel VBA.

Read More: How to Unhide Multiple Sheets in Excel (4 Ways)


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:

  • At first, 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.

Embedding VBA Code

  • A dialog box will appear
  • Now, in the Insert tab on that box, click on Module.

Embedding VBA Code

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

Embedding VBA Code

  • A new dialog box called Macro will appear. Select Unhide_All_Sheets.
  • Finally, click on the Run button to run this code.

Using xlSheetVisible Property to Unhide All Sheets Using Excel VBA

  • You will notice that all five sheets will show in the Sheet Name Bar.

Using xlSheetVisible Property to Unhide All Sheets Using Excel VBA

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.

Read More: [Fixed!] Unable to Set the Visible Property of the Worksheet Class


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:

  • At first, 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.

Embedding VBA Code

  • A dialog box will appear
  • After that, in the Insert tab on that box, click on Module.

Embedding VBA Code

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

Embedding VBA Code

  • As a result, a new dialog box called Macro will appear.
  • Then, select Unhide_Each_Sheets and click on the Run button.

Setting Worksheet Visibility to True to Unhide All Sheets Using Excel VBA

  • You will see that within a second, all five sheets will appear.

Setting Worksheet Visibility to True to Unhide All Sheets Using Excel VBA

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.

Read More: How to View All Sheets in Excel at Once (5 Easy Ways)


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.

Embedding VBA Code

  • As a result, a dialog box will appear
  • Next, in the Insert tab on that box, click on Module.

Embedding VBA Code

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

Embedding VBA Code

  • A small dialog box called Macro will appear. Select Sheets_Contain_Same_Letter.
  • Finally, click on the Run button.

Unhiding Sheets That Contains Specific Letter in Title to Unhide All Sheets Using Excel VBA

  • You will notice that all five sheets will appear in the Sheet Name Bar.

Unhiding Sheets That Contains Specific Letter in Title to Unhide All Sheets Using Excel VBA

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 (2 Effective Methods)


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.

Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo