Unhide all sheets in Excel

In Excel if you need to hide data or workings sheets you can hide multiple sheets in one action. Unfortunately Excel won’t let you unhide multiple sheets in one step (even in the latest version). To get around this limitation you can use a macro that unhides all the sheets.

When you run the macro below it will unhide all the sheets in the currently selected file. The macro doesn’t have to be in the file to work (see instructions at the bottom of the post).

MACRO WARNING: When you run a macro it can clear the undo list. This means you can’t undo anything you did before running the macro, including what the macro does. So you may want to save the file before running the macro. if the macro causes a problem then you can close the file without saving to revert to the pre-macro file.

Sub UnhideAllSheets()
Dim ws
For Each ws In Worksheets
  ws.Visible = True
Next ws
End Sub

This macro uses a For Each loop with the ws variable and the Worksheets collection.

The Worksheets collection holds all the worksheets in a file. This macro cycles through every sheet in the file and unhides it by changing the Visible property.

The file download below contains the macro – with a button to run it on the file in question.

Unhide_Macro

To run the macro on another file you need to have both files open, then activate the file you want to unhide sheets in and press Alt + F8. Find the macro UnhideAllSheets and double click it.

Click here to see how you can run a Macro off a Quick Access Toolbar icon.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.