Normal Solution to Unhide Worksheets
Usually, if we want to unhide worksheets, we can click any sheet tab and choose Unhide from the context menu. Then, there will be a list in which we can select the worksheet which we want to unhide. Click OK to finish the whole job. However, if there happens to be several of them, it can be tedious.
It’s totally necessary to do several times to unhide all hidden worksheets. By using a macro, we can quickly unhide worksheets. Copy the below macro into a Workbook:
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.
Friendly Reminder:
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.
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 unhide certain worksheets or the “very hidden” sheets, an If() statement will do the trick.
However, this solution has limited appeal like most macros. If there are a few worksheets need to be unhidden, I recommend manually unhide them. If it’s a frequent task, this solution is good or you.