How to unhide all sheets in Excel using VBA code?
To unhide all sheets in Excel, if you would like to set it manually, then refer to the image at end of this page. For changing it through VBA macro, continue reading.
In general, there are 2 options available to hide a worksheet – xlSheetHidden & other is xlSheetVeryhHidden.
What is the difference between these two?
If a worksheet is just in hidden state, it is possible to right click on any sheet, choose unhide & select the sheet. But if a sheet is veryhidden, then this manual method will not work.
In that case, this vba code can be used to unhide any sheet & make it visible.
VBA unhide all sheets or with name
Here is the code to make all worksheets in a Excel visible.
Sub UnhideAllSheets()
Dim sh As Worksheet
'Loop thru each sheet
For Each sh In ThisWorkbook.Sheets
sh.Visible = xlSheetVisible
Next
'Unhide worksheet with its name
ThisWorkbook.Sheets("Sheet2").Visible = xlSheetVisible
End SubEither you can use the name of the worksheet & use it ‘Visible’ property to make it visible. it is a straight forward method.
The last line of the code can even be execute in the immediate window & press Enter. You dont even have to write a sub procedure.
The other way of changing sheets visibility is from it property tab that is visible in the VB editor as show in this image.
That’s all about a worksheet visibility property. Here are some more external links that covers the same topic.
External References:
- Different methods to change sheets visibility property.
- How to add sheets in Excel dynamically?