Sometimes, you may be working on a workbook with too many worksheets. This may lead to a clutter of worksheets in the workbook. Excel allows users to hide and unhide the worksheets to avoid such scenarios. Sometimes, you may have a workbook with a hidden worksheet. In this tutorial, we shall discuss ways of unhiding worksheets in Excel using VBA codes.
Using Immediate Window
a) By Using Visual Basic Tool
The VBA tool can open the Immediate Window screen and unhide all the hidden worksheets. Below are the steps to follow while using this method:
1. Open the Excel application.
2. Open the Excel document that has the hidden worksheets.
3. Click on the Developer tab on the Ribbon, and then locate the Visual Basic button.
4. In the Visual Basic screen, click the View tab on the Ribbon and select the Immediate Window button.
5. An Immediate Window will open at the lower part of the screen. Copy and paste this code in this section: For each Sheet in Thisworkbook.Sheets: Sheet.Visible=True: Next Sheet
6. Then, place the cursor at the end of the code and hit the Enter Key. That is all. All the hidden worksheets will be displayed.
b) Using Keyboard Shortcuts
Keyboard shortcuts are extensively used to simplify tasks in the Excel platform. Here are the steps to follow while using the keyboard shortcut to unhide sheets:
1. Open the Excel application.
2. Open the Excel document that has the hidden worksheets.
3. Press the Alt + F11 keys on your keyboard to open the Visual Basic feature.
4. In the Visual Basic screen, press the CTRL + G keys on your keyboard.
5. An Immediate Window will open at the lower part of the screen. Copy and paste this code in this section: For each Sheet in Thisworkbook.Sheets: Sheet.Visible=True: Next Sheet
6. Then, place the cursor at the end of the code and hit the Enter Key. That is all. All the hidden worksheets will be displayed.
c) Using View Code Tool
Steps to follow:
1. Open the Excel application.
2. Open the Excel document with the hidden worksheets.
3. Right-click on one of the shown sheets, and select the View Code button from the menu.
4. In the Visual Basic screen, click the View tab on the Ribbon and select the Immediate Window button.
5. An Immediate Window will open at the lower part of the screen. Copy and paste this code in this section: For each Sheet in Thisworkbook.Sheets: Sheet.Visible=True: Next Sheet
6. Then, place the cursor at the end of the code and hit the Enter Key.
Using Macro to unhide sheets
Other than the Immediate window, Macro can be used to unhide hidden sheets in Excel. Here are the steps to follow while using this method:
1. Open the Excel application.
2. Select the dataset in which you want to add blank rows.
3. Click on the Developer tab on the Ribbon, and then locate the Visual Basic button.
4. In the Visual Basic screen, click the Insert tab on the Ribbon and select the Module button.
5. Type the following code in the empty module.
Sub UnhideAllSheets()
For Each Sheet In Sheets
Sheet.Visible = True
Next Sheet
End Sub
6. While on the Visual Basic screen, press the F5 key to run the code.