How to Unhide all Worksheets in Excel using VBA Code

Excel is a powerful tool for organizing and analyzing data. It offers various features that help users manage and manipulate their data with ease. One such feature is hiding worksheets, which can be helpful when working with large workbooks containing multiple sheets. However, sometimes users may need to unhide all the hidden worksheets.

In this article, we will see how to add and execute a VBA code script to unhide all hidden workbooks in Excel at once.

Steps to unhide all worksheets in Excel

Step 1: Open Visual Basic Editor The first step is to open the Visual Basic Editor. You can do this by pressing the Alt + F11 keys or navigating to the Developer tab and selecting Visual Basic.

Step 2: Access VBA Code Once the Visual Basic Editor is open, access the VBA code window by clicking on “Insert” and selecting “Module.”

Step 3: Paste the Code Now it’s time to paste the code that will unhide all the hidden worksheets. Here’s the code:

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

Step 4: Run the Code To run the code, simply click on the “Run” button or press the F5 key. This will execute the code, and all the hidden worksheets in the active workbook will be unhidden.

Tips:

• Before running the code, make sure to save your workbook.
• The code will only unhide the worksheets in the active workbook. If you have multiple workbooks open, you’ll need to repeat the process for each workbook.
• If you want to unhide specific worksheets, you can modify the code by replacing “ActiveWorkbook.Worksheets” with the specific worksheet names you want to unhide.
• You can also modify the code to hide all the worksheets instead of unhide them. To do this, change “ws.Visible = True” to “ws.Visible = False“.

Example: Let’s say you have a workbook with three hidden worksheets. To unhide them all, follow the steps outlined above. Here’s what the code and the workbook will look like:

Before running the code:

After running the code:

Excel VBA code can be used to unhide all the hidden worksheets in a workbook with ease. By following the steps outlined above and the tips provided, users can quickly unhide their worksheets and continue working on their data analysis.