A macro to unhide all hidden sheets in an Excel workbook

Unhiding Excel sheets is easy, but can be tedious. Use this simple macro to unhide all hidden sheets in an Excel workbook.

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:

Sub UnhideAllSheets()

'Unhide all sheets in workbook.

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

ws.Visible = xlSheetVisible

Next ws

End Sub

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.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox