• Excel Book Excerpt

Excel Unhide All Sheets

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Unhide All Sheets

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

LE10001245.jpg

Figure 1200 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.

LE10001246.jpg

Figure 1201 Unhide sheets one at a time.

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

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.

LE10001248.jpg

Figure 1202 Set up a view to remember which sheets to hide.

3. Unhide all the worksheets. If you want a quick way to do this, press Alt+F11. Press Ctrl+G. Type “for each w in activeworkbook.Worksheets : w.visible = true : next" and press Enter. Press Alt+Q.

LE10001249.jpg

Figure 1203 Run a macro in the immediate pane to unhide all sheets.

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.

LE10001250.jpg

Figure 1204 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 the Internet for Learn Excel 611.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: