Bottom line: Learn a few different ways to unhide (show) multiple sheets at the same time with a VBA macro or add-in.

Bạn đang xem: Vba hide (or unhide) a worksheet

Skill Level:Intermediate

Cannot Unhide Multiple Sheets in Excel??

As you probably know, you cannot unhide two or more sheets at the same time in Excel. The Unhide thực đơn only allows you to lớn select one sheet at a time.

*

This can make the process of unhiding multiple sheets very time consuming, especially if you want khổng lồ unhide all the sheets in the workbook. So in this article we will look at a few different ways to quickly make sheets visible.

#1 – Use the VBA Immediate Window lớn Unhide All

The fastest way to lớn make all the sheets visible in Excel is to lớn use a macro (VBA). The following line of VBA code uses a For Next Loop to lớn 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 và press EnterFor Each wsIn Sheets:ws.Visible=True:Next

The screencast below shows how khổng lồ implement these steps.

*

That line of code loops through all the worksheets in the workbook & sets each sheet"s visible property lớn “True”. This makes each sheet visible, which is the same as unhiding each sheet.

The colon character “:” used in the code allows you lớn basically combine multiple lines of code into one line. This makes it possible lớn run in the Immediate Window because the Immediate Window only evaluates one line of code at a time.

Checkout my article on 5 Ways lớn Use the VBA Immediate Window to learn more. & a big thanks khổng lồ Jan Karel Pieterse ofwww.jkp-ads.comforsharing this trick in the comments section.

#2 – Use a Macro to lớn Unhide Multiple Sheets

If you are scratching your head at that line of code in #1, this section should help explainit better.

The macro below is basically that same line of code, but it is broken up into multiple lines. This makes it much easier khổng lồ read và understand.

Sub Unhide_Multiple_Sheets()Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next wsEnd SubDownload the file that contains the macro.


The lines in the code above that start with “For” & “Next” represent a For-Next Loop Statement. Thefirstline “For Each ws In ActiveWorkbook.Worksheets” tells the macro lớn loop through each worksheet in the worksheets collection of the workbook.

*

That line also references the variable “ws” & sets it lớn 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 lớn the first line of code within the loop and evaluates it again. It continues khổng lồ 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 lớn be visible (xlSheetVisible). The visible property has three different properties lớn choose from:

xlSheetHiddenxlSheetVeryHiddenxlSheetVisible
*

You can also phối the property khổng lồ “True”, which works the same as xlSheetVisible.

Xem thêm:

Here is the documentation on the VBA Visible property from Microsoft. And checkout my article on the For Next Loop for a detailed explanation of how it works.

Unhide Sheets That Contain a Specific Name

What if we only want lớn unhide the sheets that contain the word “pivot” in the sheet name?

We can địa chỉ cửa hàng a simple IF statement to lớn 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, "pivot") > 0 Then ws.Visible = xlSheetVisible end If Next wsEnd SubDownload the file that contains the macro.


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, và the function is similar khổng lồ the search or FIND functions in Excel.

So in this case we are looking for any sheet that contains the word “pivot” 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, “pivot”) > 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 lớn True và the next line of code will be run lớn unhide the sheet.

If the word “pivot” is NOT found in the sheet name, then the IF statement will evaluate lớn False & 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).

This macro works great if you are hiding và unhiding sheets every day/week/month for a report that you maintain. Run the macro to unhide specific sheets when you mở cửa the workbook. After you are finished, run the same codebut change the visible property to lớn xlSheetHidden to lớn re-hide the sheets (you can create a new macro for this).

#3 – Use Tab Hound or Tab Control

If all this code is making your head spin, the Tab Hound Add-in allows you to unhide multiple sheets with the click of a view buttons.

The screencast below shows how simple this is.

*

The Tab Hound window contains a các mục of all the sheets in the workbook. You can select multiple sheets in the list, then press the Unhide button khổng lồ unhide all the selected sheets.

This makes the process of unhiding multiple sheets really fast!

Tab Hound also contains additional ways khổng lồ filter the sheet list. You can type a search in the tìm kiếm box, filter for all visible or hidden tabs, và even filter by tab color. This makes it easy lớn find the sheets you are looking for & then perform actions on them like hiding/unhiding.

This video clip also shows how to quickly hide & unhide multiple sheets with Tab Hound.

Click here khổng lồ learn more about Tab Hound

Publishing Reports? Tab Control Will Save You Time

If you are producing weekly or monthly reports, and want lớn make sure all the right sheets are hidden before you send it out, the Tab Control add-in can save you a lot of time.

Here is a scenario that we commonly face…

We need to lớn update a workbook with new data this week & make some changes before emailing it out. Those updates require us lớn unhide a few sheets, make the changes, then hide the sheets again. It can be a time consuming process if you have khổng lồ hide/unhide a lot of sheets.

*

Tab Control will cài đặt a control sheet in your workbook with a menu of all the worksheet names. You can mix the visible property for each sheet in the list, then click a button khổng lồ hide or unhide all the sheets based on the list.

This means you can thiết đặt the control sheet lớn a specific view, so only specific worksheets are visible. Click the “Run Update” button và all sheets will be hidden/unhidden based on your control sheet. This ensures that your report will show the correct sheets everytime you send it out, và make you look really organized.