While working on excel, we all face similar situations when we do have multiple worksheets hidden and we spend half of our day to unhide them one by one. Instead of unhiding them one by one we can use the following techniques to unhide them at once.1. Using VBA immediate window:
The fastest way to make all the sheets visible in Excel is to use a macro (VBA). The following line of VBA code uses a For Next Loop to loop through each sheet in the active workbook and make each sheet visible.
For Each ws In Sheets:ws.Visible=True:Next
You can run this code in the VB Editor’s Immediate Window in three easy steps:
- Alt+F11 (opens the VB Editor Window)
- Ctrl+G (opens the Immediate Window)
- Paste the following line of code in the Immediate Window and press Enter
The above line of code loops through all the worksheets in the workbook and sets each sheet’s visible property to “True”. This makes each sheet visible, which is the same as unhiding each sheet.
The colon character “:” used in the code allows you to basically combine multiple lines of code into one line. This makes it possible to run in the Immediate Window because the Immediate Window only evaluates one line of code at a time.
2. Using VBA macro to unhide/Hide multiple sheets:
The following macro is basically the same line of code mentioned in point 1, but it is broken up into multiple lines. This makes it much easier to read and understand.
Sub Unhide_Multiple_Sheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub
The lines in the above code that start with “For” and “Next” represent a For-Next Loop Statement. The first line “For Each ws In ActiveWorkbook.Worksheets” tells the macro to loop through each worksheet in the worksheets collection of the workbook.
That line also references the variable “ws” and sets it to represent the worksheet object. This means that “ws” temporarily represents the current worksheet in the loop.
When the “Next ws” line of code is hit, the macro jumps back up to the first line of code within the loop and evaluates it again. It continues to loop through all the sheets in the workbook’s worksheet collection (Activeworkbook.Worksheets).
We can then use “ws” inside the loop to change the current worksheet’s properties. In this case we are setting the “Visible” property of the sheet to be visible (xlSheetVisible).
The visible property has three different properties to choose from:
- xlSheetHidden
- xlSheetVeryHidden
- xlSheetVisible
You can also set the property to “True”, which works the same as xlSheetVisible.
3. Unhiding Sheets with a Specific Name
Also at times we need to unhide the sheets that contain a specific word.
Lets imagine that the word “xyz” in the sheet name?
We can add a simple IF statement to the macro to only unhide sheets that contain a specific name or text.
Sub Unhide_Sheets_Containing()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If InStr(ws.Name, "xyz") > 0 Then
ws.Visible = xlSheetVisible
End If
Next ws
End Sub
The InStr function searches for text in a string and returns the position of the first occurrence of the text. It is short for InString, and the function is like the SEARCH or FIND functions in Excel.
So, in this case we are looking for any sheet that contains the word “xyz” in the sheet name. The “ws.name” reference returns the name of the worksheet that is currently being evaluated in the For-Next loop.
So this line “If InStr(ws.Name, “xyz”) > 0 Then” basically says, if the word “pivot” is found in the sheet name then the Instr function will return a number greater than zero. The IF statement will evaluate to True and the next line of code will be run to unhide the sheet.
If the word “xyz” is NOT found in the sheet name, then the IF statement will evaluate to False and the code will skip all lines until it gets to the “End If” line. Therefore, the sheet that is currently being evaluated in the loop will keep its current visible property (visible or hidden).
These macro’s will be more helpful if you are hiding and unhiding sheets every day/week/month for a report. Run the macro to unhide specific sheets when you open the workbook. If you want to re-hide the sheets back then run the same code but change the visible property to xlSheetHidden.
