Right up front, this does not require the use of macro-enabled workbooks. This technique can be performed in any Excel workbook.
- Open the Visual Basic Editor by pressing Alt-F11 on the keyboard or right click on any sheet tab and select View Code.
Don’t concern yourself with what you see in the ensuing window; all of that is for another day.
- Activate the Immediate Window by clicking View -> Immediate Window (or CTRL-G).
Now we will run a macro. This macro will loop through all the hidden sheets and revert their visibility states to “visible”. We will use a “For…Each” collection loop to perform this operation.
NOTE: If you are interested in learning about this command and many other useful things macros can do for you, visit the links at the end of this tutorial.
- In the Immediate window, type
for each sh in worksheets: sh.visible=true: next sh
All the sheets have returned to a visible state.
What does that code mean? Let’s break down the code.
for each sh in worksheets
This establishes a collection (list) of all worksheets and allows us to refer to each sheet individually with the alias “sh”.
With the first sheet in the collection, set the visible property to “true”. This makes the sheet visible to the user.
This selects the next sheet in the collection and returns to the first statement to repeat the process.
This process will repeat for as many sheets as are in the collection.
If this code is something you will use frequently, you can save the code in a Notepad file and then copy/paste it back into the Immediate Window whenever needed.