Neale Blackwood plays Hide and Go Sheet with Excel
Q: I hide a number of sheets in the workbooks I create because they are used for workings. Is there an easy way to make them all visible at once?
A:The following macro will unhide all the sheets in the active workbook.
Sub UnHideAllSheets()
'this macro unhides all the sheets in the active workbook
Dim sht As Worksheet
For Each sht In Worksheets
sht.Visible = xlSheetVisible
Next sht
End Sub
The line For Each sht In Worksheets will go through each sheet in the workbook. This will ignore any chart sheets in the workbook.
If you want to hide all the sheets in a workbook be aware that you must leave at least one sheet visible, otherwise Excel will return an error. The following code will hide all sheets except the active sheet.
Sub HideAllSheets()
'this macro hides all sheets except the active sheet
Dim sht As Worksheet
For Each sht In Worksheets
If sht.Name <> ActiveSheet.Name Then
sht.Visible = xlSheetHidden
End If
Next sht
End Sub
Neale Blackwood CPA is a senior business analyst with Access Analytic Solutions, which provides financial modelling and management reporting services, as well as advanced Excel training.
Further information
Reference: October 2008, volume 78:09, p. 20
