How to Unhide Multiple Worksheets at Once in Excel Using VBA
Question details
The user needs a method to unhide multiple hidden worksheets at the same time, overcoming the limitation of the standard Excel interface.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing a large workbook with numerous hidden sheets and needing to reveal several or all of them instantly without unhiding each one individually.
- Observed behavior
- Excel's standard user interface only allows a user to select and unhide one hidden worksheet at a time, making it a tedious process for workbooks with many hidden sheets.
Before running any VBA macros, make sure to save a backup copy of your workbook, as macro executions cannot be reversed using the standard Undo feature.
Use a VBA Macro to Unhide All Hidden Worksheets
This solution uses a simple VBA script to loop through all worksheets in your workbook and instantly unhide any sheet that is currently hidden.
Since the default Excel interface restricts you to unhiding one sheet at a time, utilizing Visual Basic for Applications (VBA) is the most efficient workaround. The code below checks the visibility status of every sheet and automatically changes it to visible.
Press the keyboard shortcut ALT + F11 to launch the Microsoft Visual Basic for Applications window.
In the top menu bar, click on 'Insert' and select 'Module' from the drop-down list to create a blank workspace for your code.
Copy and paste the following code into the module window: Sub ShowAllHiddenSheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Sheets If ws.Visible = xlSheetHidden Then ws.Visible = xlSheetVisible End If Next ws End Sub
Close the VBA editor to return to your workbook. Press ALT + F8 to open the Macro dialog box, select 'ShowAllHiddenSheets' from the list, and click the 'Run' button.
Unhide Specific Worksheets Using VBA
If you only want to unhide a select few sheets rather than all of them, you can specify the exact sheet names within your VBA code.
Run Macros and Unhide Sheets Easily with WPS Office
WPS Spreadsheets provides comprehensive data management features and fully supports VBA macros, allowing you to run your Excel VBA scripts seamlessly. Manage your worksheets efficiently with a lightweight and highly compatible office suite.
- 1. Open Your Workbook in WPS: Launch WPS Spreadsheets and open the workbook containing your hidden sheets.
- 2. Access the VBA Editor: Go to the 'Tools' tab on the ribbon and click on 'Macros', or simply press ALT + F11 to open the VBA Editor.
- 3. Insert the Code: Click 'Insert' > 'Module', paste the worksheet unhiding VBA code, and close the editor.
- 4. Run the Macro: Press ALT + F8, select your newly created macro, and click 'Run' to reveal your hidden sheets instantly.
Frequently Asked Questions
Why doesn't Excel let me unhide multiple sheets using the Ctrl or Shift keys?
Excel's default user interface restricts the 'Unhide' dialog box to selecting only one sheet at a time. Using a VBA macro is the standard workaround to bypass this interface limitation and unhide them all simultaneously.
Will this macro unhide 'Very Hidden' sheets?
The provided code specifically checks for 'xlSheetHidden'. If your sheets are set to 'xlSheetVeryHidden', they will not be unhidden by the first script. To unhide them, you can remove the 'If' condition in the loop, or change the condition to check for 'xlSheetVeryHidden'.
How do I save my workbook after adding a VBA macro?
To keep the VBA macro for future use, you must save your file as an Excel Macro-Enabled Workbook (.xlsm). If you save it as a standard Excel Workbook (.xlsx), the macro code will be automatically removed upon saving.
