We hide sheets for many reasons, but mostly, to keep other people out of them. We rarely hide them from ourselves. When you need to update or fix a workbook for a user, you have to remember the hidden sheets and then unhide them – which is easy enough, unless you removed that functionality from the workbook!
To unhide sheets, click any sheet tab and choose Unhide from the context menu. Then, select the sheet you want to unhide from the list and click OK. Although easy, unhiding sheets in this manner is tedious if there happens to be several of them.
Doing this several times to unhide all hidden sheets isn’t necessary. Here’s a quick macro that you can copy into almost any workbook to quickly unhide sheets:
‘Unhide all sheets in workbook.
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
In a nutshell, a For Each loop cycles through all the sheets in the Worksheets collection and sets each sheet’s Visible property to true. This macro will even unhide sheets you hide via the Visual Basic Editor properties (xlSheetVeryHidden) so be careful how you apply it.
To run the macro, click Macros in the Code group on the Developer tab. Or, add it to the QAT or a custom tab.
Like most macros, this one has limited appeal. If you have only a few hidden sheets and you seldom need to unhide them, it’s just as easy to manually unhide them. If, on the other hand, this is a frequent task, you’ll probably find this one useful.
It’s a good demonstration of how easy it is to cycle through an object collection. You could add an If() statement that checks for the Visible property and then change only the ones that require it, but this loop is more efficient. Just reset them all; in this case, an If() just adds more work. However, if you want to avoid unhiding certain sheets or the “very hidden” sheets, an If() statement will do the trick.
Subscribe to the Microsoft Weekly Newsletter
Be your company's Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets. Delivered Mondays and Wednesdays