Microsoft

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

7 comments
DBlayney
DBlayney

I don't agree. I have a couple of applications written in Excel VBA and I hide sheets for two reasons: First: There are sheets that contain control and status information that has nothing to do with the users and would only tempt them to try to change settings (the sheets are protected but this is not the issue). Second: Depending on the status of the application and what is being done at the time, some sheets are relevant and some are not. The irrelevant ones are hidden to make it easier for the user to find what he/she needs quickly and without confusion.

briant11
briant11

Hello, My philosophy is that if you don't want to to deal with hassles later on, you should not select to hide your spreadsheets in the first place. However, some people might like to keep some of their data secret but I'm sometimes a bit lazy so I came up with the solution that if you don't choose the hide option in Excel, you'll not have to bother with making it appear again the next time you use the program.

hammacka
hammacka

Or, use ASAP Utilities and select "Unhide All Sheets" from the menu. Most Excel users should have this anyway.

andrewmrichards
andrewmrichards

Morning all This is a variation on a pair of macros I use all the time. The issue is that I have a system which exports a lot of data on various organisations - one sheet per organisation. The sheet is then named with the name of the organisation, and there may be 100 or more sheets in a single workbook. So, I have a macro called "Show All Sheets" which is virtually identical to the one above. But I also have one called "FindSheetByName" which prompts the user for a name, then searches for a sheet containing the provided text. If anyone would find it useful, the code is on my website here: http://www.theitservice.co.uk/tips-and-tricks/excel/locate_a_sheet_by_name.html Regards Andrew Richards

gwyn909
gwyn909

It would be nice to have keystroke macros that worked properly as in Lotus 1-2-3 under dos. I don't particularly like having to debug Basic programmes for a simple keystroke macro.

chilakwad
chilakwad

Hi, it would be great if we can get some more ( small ) tips about excel to make life easier. Regards,

ssharkins
ssharkins

If you can supply some problems, I'll be glad to try to supply some solutions! Thanks!