Unhide All Sheets

October 04, 2023 - by Bill Jelen

Problem: If you use Group Mode, you can hide a bunch of worksheets in one command.

Home, Format, Hide & Unhide, Hide Sheet. — Figure 1348. Hide many worksheets in one command.

However, there is no way to unhide all of the worksheets in a single command. You have to do Home, Format, Hide & Unhide, Unhide Sheets to get to this dialog. You can not select multiple worksheets here, so you have to repeat that command for every worksheet.

Strategy: Use the View Manager. Create one view with the worksheets hidden. Create another view with the worksheets visible.

If you hide six sheets, the Unhide dialog box forces you to choose one sheet and click OK. There is no way to use this dialog to Unhide all sheets at once. — Figure 1349. Unhide sheets one at a time.

To solve the current problem, follow these steps:

1. Select View, Custom Views, Add.
2. Assuming the worksheets are currently hidden, use a name such as SheetsHidden.
3. Unhide all the worksheets. If you want a quick way to do this, press Alt+F11. Press Ctrl+G. Type the following:
```
for each w in activeworkbook.Worksheets : w.visible = true : next
```
and press Enter. Press Alt+Q.
4. Now that the worksheets are unhidden, select View, Custom Views, Add. Use a name such as Unhidden.
5. To quickly switch between the two views, use View, Custom Views. Select the correct view and click Show.

The Add View dialog box. The Name of this view is SheetsHidden. Two checkboxes are selected: Print Settings and Hidden Rows, Columns and Filter Settings. — Figure 1350. Set up a view to remember which sheets to hide.

Two custom views are created: SheetsHidden and Unhidden. Choose Unhidden and click Show. This will unhide all sheets. — Figure 1351. Switch to a different view.

Gotcha: If any of your worksheets use a table, you can not use views. In this case, I would add a macro to your personal macro workbook with the code from step 3. For a demo of this, search YouTube for Learn Excel 611.

This article is an excerpt from Power Excel With MrExcel

Title photo by Nik on Unsplash