Unhide all sheets in a Workbook

In my Excel workbooks I often have VBA hide worksheets (tabs) that I am not using as “Veryhidden”. Very hidden means that when you right-click on the tabs for the unhide option, very hidden sheets do not show in the list. This is useful so that end users cannot (easily) access the sheets that hold workings/settings that I use as variables in macros.

The VBA code to “veryhide” a worksheet is –

Sheets("SheetName").Visible = 2

The downside of this is that when I want to see it again to check settings or update it, it’s a bit of a faff to unhide, you must go into the Visual Basic window, select the sheet and change the Visible setting.

So instead I use this quick VBA Sub which will unhide all worksheets in a Workbook.

Sub UnhideAllSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub

This unhides all sheets in a workbook so that I can see whats going on and make any necessary changes.

If this post was useful to you, or you have a piece of VBA you find invaluable leave a comment and share.

Add a Comment

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.