How to unhide sheets in Excel
If you want to see just one or two hidden sheets, here’s how you can quickly unhide them:
Note. Excel’s Unhide option only allows you to select one sheet at a time. To unhide multiple sheets, you will have to repeat the above steps for each worksheet individually or you can unhide all sheets in one go by using the below macros.
How to unhide sheets in Excel with VBA
In situations when you have multiple hidden worksheets, unhiding them one-by-one might be very time consuming, especially if you’d like to unhide all the sheets in your workbook. Fortunately, you can automate the process with one of the following macros.
How to unhide all sheets in Excel
This small macro makes all hidden sheets in an active workbook visible at once, without disturbing you with any notifications.
Sub Unhide_All_Sheets() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets wks.Visible = xlSheetVisible Next wks
End Sub
Show all hidden sheets and display their count
Like the above one, this macro also displays all hidden sheets in a workbook. The difference is that upon completion, it shows a dialog box informing the user how many sheets have been unhidden:
Sub Unhide_All_Sheets_Count() Dim wks As Worksheet Dim count As Integer count = 0 For Each wks In ActiveWorkbook.Worksheets wks.Visible = xlSheetVisible count = count + 1 End If Next wks MsgBox count & " worksheets have been unhidden.", vbOKOnly, "Unhiding worksheets" Else MsgBox "No hidden worksheets have been found.", vbOKOnly, "Unhiding worksheets" End If
End Sub
Unhide multiple sheets that you select
If you’d rather not unhide all worksheets at once, but only those that the user explicitly agrees to make visible, then have the macro ask about each hidden sheet individually, like this:
Sub Unhide_Selected_Sheets() Dim wks As Worksheet Dim MsgResult As VbMsgBoxResult For Each wks In ActiveWorkbook.Worksheets If wks.Visible = xlSheetHidden Then MsgResult = MsgBox("Unhide sheet " & chúng tôi & "?", vbYesNo, "Unhiding worksheets") If MsgResult = vbYes Then wks.Visible = xlSheetVisible End If Next
End Sub
Unhide worksheets with a specific word in the sheet name
In situations when you only want to unhide sheets containing certain text in the their names, add an IF statement to the macro that will check the name of each hidden worksheet and unhide only those sheets that contain the text you specify.
In this example, we unhide sheets with the word ” report” in the name. The macro will display sheets such as Report, Report 1, July report, and the like.
To unhide worksheets whose names contain some other word, replace ” report” in the following code with your own text.
Sub Unhide_Sheets_Contain() Dim wks As Worksheet Dim count As Integer count = 0 For Each wks In ActiveWorkbook.Worksheets wks.Visible = xlSheetVisible count = count + 1 End If Next wks MsgBox count & " worksheets have been unhidden.", vbOKOnly, "Unhiding worksheets" Else MsgBox "No hidden worksheets with the specified name have been found.", vbOKOnly, "Unhiding worksheets" End If
End Sub
How to use the macros to unhide sheets in Excel
To use the macros in your worksheet, you can either copy/paste the code in the Visual Basic Editor or download the workbook with the macros and run them from there.
How to insert the macro in your workbook
You can add any of the above macros to your workbook in this way:
Open the workbook with hidden sheets.
Press Alt + F11 to open the Visual Basic Editor.
Paste the code in the Code window.
Press F5 to run the macro.
For the detailed step-by-step instructions, please see How to insert and run VBA code in Excel.
Download the workbook with the macros
Alternatively, you can download our sample workbook to unhide sheets in Excel that contains all of the macros discussed in this tutorial:
Unhide_All_Sheets – unhide all worksheets in an active workbook momentarily and silently.
Unhide_All_Sheets_Count – show all hidden sheets along with their count.
Unhide_Selected_Sheets – display hidden sheets you choose to unhide.
Unhide_Sheets_Contain – unhide worksheets whose names contain a specific word or text.
To run the macros in your Excel, you do the following:
Open the downloaded workbook and enable the macros if prompted.
Open your own workbook in which you want to see hidden sheets.
For example, to unhide all sheets in your Excel file and display the hidden sheets count, you run this macro:
How to show hidden sheets in Excel by creating a custom view
So, what we are going to do now is create the Show All Sheets custom view. Here’s how:
That’s it! All hidden sheets will be shown immediately.
Note. This method does not show very hidden sheets. The only way to view such sheets is to unhide them with VBA.
Cannot unhide sheets in Excel – problems and solutions
If you are unable to unhide certain sheets in your Excel, the following troubleshooting tips may shed some light why.
1. The workbook is protected
2. Worksheets are very hidden
If your worksheets are hidden by VBA code that makes them very hidden (assigns the xlSheetVeryHidden property), such worksheets cannot be displayed by using the Unhide command. To unhide very hidden sheets, you need to change the property from xlSheetVeryHidden to xlSheetVisible from within the Visual Basic Editor or run this VBA code.
3. There are no hidden sheets in the workbook
This is how you unhide sheets in Excel. If you are curious to know how to hide or unhide other objects such as rows, columns or formulas, you will find full details in the below articles. I thank you for reading and hope to see you on our blog next week!
Macros to unhide worksheets in Excel
You may also be interested in