How to unhide sheets in Excel: show multiple or all hidden sheets

The tutorial explain how to unhide worksheet in excel 2016, 2013, 2010 and depleted. You will teach how to cursorily unhide worksheet aside right-clicking and how to unhide all tabloid astatine a clock time with VBA code .
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 :

  1. In your Excel workbook, right-click any sheet tab and select Unhide… from the context menu.
  2. In the Unhide box, select the hidden sheet you want to display and click OK (or double-click the sheet name). Done!

Unhide sheets in Excel via the right-click menu.

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

Unhide worksheet using the ribbon.
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 :
Unhide all sheets in Excel with VBA

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 :
Unhide multiple sheets with VBA

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 :

  1. Open the workbook with hidden sheets.
  2. Press elevation + F11 to open the Visual Basic Editor.
  3. On the left pane, right-click ThisWorkbook and select Insert > Module from the context menu.
  4. Paste the code in the Code window.
  5. 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 :

  1. Open the downloaded workbook and enable the macros if prompted.
  2. Open your own workbook in which you want to see hidden sheets.
  3. 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 :
A macro to unhide all sheets in Excel

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 :

  1. Make sure all the spreadsheets in your workbook are visible. This tip shows how to quickly check workbook for hidden sheets.
  2. Go to the View tab > Workbook Views group, and click the Custom Views button.
    Custom Views in Excel
  3. The Custom View dialog box will show up, and you click Add…
    Adding a new custom view
  4. in the Add View dialog box, type the name for your custom view, for example ShowAllSheets, and click OK.
    Creating ShowAllSheets view

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.
Use a custom view to show all hidden sheets at once.
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.
Check if a workbook contains any hidden sheets.
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

You may also be interested in

Related Posts

Trả lời

Email của bạn sẽ không được hiển thị công khai.