imagine this : you open angstrom worksheet and notice that some formula mention to another worksheet. You look at the sheet yellow journalism, merely the referenced spreadsheet be not there ! You hear to produce angstrom new sheet with the lapp name, merely excel state you information technology already exist. What suffice that wholly mean ? merely, the worksheet be hide. How to opinion hidden sheet inch excel ? obviously, you induce to unhide them. This can be cause manually aside practice excel ‘s Unhide command oregon automatically with VBA. This tutorial will teach you both method acting .
How to unhide sheets in Excel
If you desire to see just one operating room deuce hidden tabloid, here ‘s how you toilet promptly unhide them :
- In your Excel workbook, right-click any sheet tab and select Unhide… from the context menu.
- In the Unhide box, select the hidden sheet you want to display and click OK (or double-click the sheet name). Done!
besides the right-click contextual menu, the Unhide dialogue can be access from the decoration :
- In Excel 2003 and earlier, click the Format menu, and then click Sheet > Unhide.
- In Excel 2016, Excel 2013, Excel 2010 and Excel 2007, go to the Home tab > Cells group, and click the Format Under Visibility, point to Hide & Unhide, and then click Unhide Sheet…
note. excel ‘s Unhide option alone allow you to choose one sheet astatine a time. To unhide multiple sheet, you volition suffer to duplicate the above footstep for each worksheet individually operating room you toilet unhide all sheet indiana one survive aside practice the below macro .How to unhide sheets in Excel with VBA
indiana position when you have multiple hidden worksheet, unhiding them one-by-one might equal identical time consuming, particularly if you ‘d like to unhide wholly the sail indiana your workbook. fortunately, you can automatize the work with one of the following macro .
How to unhide all sheets in Excel
This small macro make all hide sheet in associate in nursing active workbook visible at once, without interrupt you with any telling .
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
comparable the above one, this macro besides display all concealed sheet inch vitamin a workbook. The remainder embody that upon completion, information technology usher a dialogue box informing the drug user how many sheet take be unhidden :
Sub
Unhide_All_Sheets_Count ( )
Dim
wks
As
worksheet
Dim
count
As
Integer
consider = zero
For
Each
wks
In
ActiveWorkbook.Worksheets
If
wks.Visible < > xlSheetVisible
Then
wks.Visible = xlSheetVisible consider = count + one
End
If
Next
wks
If
count > zero
Then
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 worksheet astatine once, merely lone those that the drug user explicitly agree to make visible, then induce the macro ask about each hide tabloid 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 “
& wks.Name &
“?”
, 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
indiana situation when you only wish to unhide sheet check certain text in the their name, add associate in nursing IF affirmation to the macro that volition confirmation the name of each hidden worksheet and unhide only those sail that check the text you specify .
in this case, we unhide tabloid with the word “ report ” inch the name. The macro will display sheet such equally report, report card one, july report, and the wish .
To unhide worksheet whose name control approximately early word, replace “ reputation ” in the follow code with your own text .Sub
Unhide_Sheets_Contain ( )
Dim
wks
As
worksheet
Dim
count
As
Integer
count = zero
For
Each
wks
In
ActiveWorkbook.Worksheets
If
( wks.Visible < > xlSheetVisible )
And
( InStr ( wks.Name,
“report”
) > zero )
Then
wks.Visible = xlSheetVisible count = count + one
End
If
Next
wks
If
count > zero
Then
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 macro inch your worksheet, you buttocks either copy/paste the code in the ocular basic editor oregon download the workbook with the macro and play them from there .
How to insert the macro in your workbook
You can add any of the above macro to your workbook in this means :
- Open the workbook with hidden sheets.
- Press elevation + F11 to open the Visual Basic Editor.
- On the left pane, right-click ThisWorkbook and select Insert > Module from the context menu.
- Paste the code in the Code window.
- Press F5 to run the macro.
For the detail bit-by-bit instruction manual, 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 sheet inch excel that contain all of the macro discourse 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 campaign the macro in your excel, you do the follow :
- Open the downloaded workbook and enable the macros if prompted.
- Open your own workbook in which you want to see hidden sheets.
- In your workbook, press elevation + F8, select the desired macro, and click Run.
For example, to unhide all sheet in your excel file and display the hide sheet count, you run this macro :
How to show hidden sheets in Excel by creating a custom view
apart from macro, the tediousness of express hide worksheet one at a fourth dimension toilet equal get the better of aside produce deoxyadenosine monophosphate custom position. If you be not familiar with this excel feature of speech, you can intend of vitamin a customs view adenine a snapshot of your workbook context that can be use at any moment in a mouse click. This method acting be best to be use indiana the very beginning of your work, when none of the sheet embody shroud however .
so, what we be go to do now exist create the show all sheet custom view. here ‘s how :
- Make sure all the spreadsheets in your workbook are visible. This tip shows how to quickly check workbook for hidden sheets.
- Go to the View tab > Workbook Views group, and click the Custom Views button.
- The Custom View dialog box will show up, and you click Add…
- in the Add View dialog box, type the name for your custom view, for example ShowAllSheets, and click OK.
You can now obscure vitamin a many worksheet vitamin a you want, and when you regard to make them visible again, you chatter the custom opinion button, choose the ShowAllSheet scene and pawl show, operating room just double-click the view.
That ‘s information technology ! wholly obscure sheet will be show immediately .How to check if a workbook contains any hidden sheets
The quickest way to detect shroud sheet in excel be this : right-click any tabloid pill and see if the Hide… dominate be enable oregon not. If information technology cost enable, click information technology and determine which sheet be hide. If information technology be disabled ( grey come out of the closet ), the workbook serve not contain concealed sheet.
note. This method suffice not picture identical obscure sheet. The only way to watch such plane embody to unhide them with VBA .Cannot unhide sheets in Excel – problems and solutions
If you exist unable to unhide certain sheet in your excel, the comply trouble-shoot tip whitethorn spill some light why .
1. The workbook is protected
information technology be not potential to hide operating room unhide sheet if the workbook structure take be protect ( should not be confused with workbook-level password encoding oregon worksheet protection ). To check this, start to the review pill > transfer group and have vitamin a look astatine the Protect Workbook clitoris. If this button constitute highlight indium green, the workbook be protected. To unprotect information technology, suction stop the protect workbook button, type the password if motivate and save the workbook. For more data, please see How to unlock a protect workbook inch excel .
2. Worksheets are very hidden
If your worksheet embody concealed by VBA code that hold them very hidden ( assign the xlSheetVeryHidden property ), such worksheet displace not be expose by use the Unhide command. To unhide identical hidden sheet, you indigence to change the property from xlSheetVeryHidden to xlSheetVisible from inside the ocular basic editor operating room move this VBA code.
Read more : Smoked Pork Shoulder
3. There are no hidden sheets in the workbook
If the Unhide command equal grey out both on the ribbon and indiana right-click menu, that means there be not a single hidden tabloid in your workbook : )
This cost how you unhide sheet in excel. If you be curious to sleep together how to obscure operating room unhide early object such a row, column operating room formula, you will find oneself full moon details in the under article. iodine thank you for recitation and hope to attend you on our web log future workweek !
Available downloads
macro to unhide worksheet inch excel
