VBA to Unhide All Hidden Sheets
Excel currently has no unhide All feature for worksheets and as an Excel user it can be tedious right clicking on a sheet and clicking Unhide for each hidden sheet in the workbook. The alternative is to produce some code which will unhide all of the sheets in the workbook and put it in your Personal Macro Workbook. The following simple lines of code will unhide all of the hidden sheets in a workbook.
The following YouTube video takes you through some of the methods explained in this post. Enjoy.
The following lines of VBA code will unhide all sheets in a workbook.
Sub UnhideMe() 'Unhide all of the sheets which are hidden in an Excel file.
For Each ws In Sheets
End Sub
Sub UnhideMe() 'Unhide all of the sheets which are hidden in an Excel file.
Dim ws As Worksheet
For Each ws In Sheets
ws.Visible=True
Next ws
End Sub
If you only want to unhide certain sheets then the following should do exactly that.
Sub UnhideMe2() 'Unhide specific sheets which are hidden in an Excel file.
Dim ar As Variant
Dim i As Long
'In Case either or both sheets are visible.
ar=[{"Sheet1", "Sheet2"}]
The above will hide sheet1 and Sheet2.
Hide All but the Far Right Sheet
The following code snippet assumes the sheet you want visible is the right most sheet in your workbook.
Sub hide() 'Hide all of the sheets which are hidden in an Excel file.
Dim i As Integer
Dim ar As Variant
Dim i As Long
'In Case either or both sheets are visible.
ar=[{"Sheet1", "Sheet2"}]
For i = 1 to Ubound(ar)
End Sub
Sheets(i).Visible = False
Next iThe above will hide sheet1 and Sheet2.
Hide All but the Far Right Sheet
The following code snippet assumes the sheet you want visible is the right most sheet in your workbook.
Sub hide() 'Hide all of the sheets which are hidden in an Excel file.
Dim i As Integer
For i=1 To Worksheets.Count - 1
End Sub
Sheets(i).Visible=False
Next i
