Hiding And Unhiding Sheets

// Hiding And Unhiding Sheets

Hiding and unhiding sheets

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?

By Neale Blackwood
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

2017-03-08T03:53:57+00:00 VBA – Macros|