Bạn đang xem bài viết 3 Ways To Unhide Multiple Sheets In Excel + Vba Macros được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Bottom line: Learn a few different ways to unhide (show) multiple sheets at the same time with a VBA macro or add-in.
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 menu only allows you to select one sheet at a time.
#1 – Use the VBA Immediate Window to Unhide All
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 EnterFor Each ws In Sheets:ws.Visible=True:Next
The screencast below shows how to implement these steps.
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 – Use a Macro to Unhide Multiple Sheets
If you are scratching your head at that line of code in #1, this section should help explain it better.
The macro below is basically that same line of code, 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 SubDownload the file that contains the macro.
Unhide Multiple Sheets chúng tôi (64.2 KB)
The lines in the code above 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.
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
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 to unhide the sheets that contain the word “pivot” 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 ws.Visible = xlSheetVisible End If Next ws End SubDownload the file that contains the macro.
Unhide Multiple Sheets chúng tôi (64.2 KB)
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 similar to 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.
If the word “pivot” 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).
This macro works great if you are hiding and unhiding sheets every day/week/month for a report that you maintain. Run the macro to unhide specific sheets when you open the workbook. After you are finished, run the same code but change the visible property to xlSheetHidden to re-hide the sheets (you can create a new macro for this).
#3 – Use Tab Hound or Tab Control
The screencast below shows how simple this is.
This makes the process of unhiding multiple sheets really fast!
Tab Hound also contains additional ways to filter the sheet list. You can type a search in the search box, filter for all visible or hidden tabs, and even filter by tab color. This makes it easy to find the sheets you are looking for and then perform actions on them like hiding/unhiding.
This video also shows how to quickly hide and unhide multiple sheets with Tab Hound.
If you are producing weekly or monthly reports, and want to 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 update a workbook with new data this week and make some changes before emailing it out. Those updates require us to unhide a few sheets, make the changes, then hide the sheets again. It can be a time consuming process if you have to hide/unhide a lot of sheets.
The Tab Control add-in is included with Tab Hound.
Unhiding multiple sheets at the same time in Excel will require code or a macro. There is one other way using Custom Views, but it has limitations if you use Excel Tables (and I love Tables).
Hopefully you learned some VBA code that you can implement. You can also add the macros to your Personal Macro workbook to run them anytime you need.
If coding isn’t your thing then checkout the Tab Hound add-in. It will save you time and make your life a lot easier. (win-win!) 🙂
A Macro To Unhide All Hidden Sheets In An Excel Workbook
Unhiding Excel sheets is easy, but can be tedious. Use this simple macro to unhide all hidden sheets in an Excel workbook.
We hide sheets for many reasons, but mostly, to keep other people out of them. We rarely hide them from ourselves. When you need to update or fix a workbook for a user, you have to remember the hidden sheets and then unhide them – which is easy enough, unless you removed that functionality from the workbook!
Doing this several times to unhide all hidden sheets isn’t necessary. Here’s a quick macro that you can copy into almost any workbook to quickly unhide sheets:
Sub UnhideAllSheets()
‘Unhide all sheets in workbook.
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub
In a nutshell, a For Each loop cycles through all the sheets in the Worksheets collection and sets each sheet’s Visible property to true. This macro will even unhide sheets you hide via the Visual Basic Editor properties (xlSheetVeryHidden) so be careful how you apply it.
Like most macros, this one has limited appeal. If you have only a few hidden sheets and you seldom need to unhide them, it’s just as easy to manually unhide them. If, on the other hand, this is a frequent task, you’ll probably find this one useful.
It’s a good demonstration of how easy it is to cycle through an object collection. You could add an If() statement that checks for the Visible property and then change only the ones that require it, but this loop is more efficient. Just reset them all; in this case, an If() just adds more work. However, if you want to avoid unhiding certain sheets or the “very hidden” sheets, an If() statement will do the trick.
How To Unhide All Worksheets &Amp; Sheets In Excel?
How to unhide all worksheets & sheets in Excel?
This article provides several methods to unhide all hidden sheets & worksheets in Excel step by step.
Unhide all hidden worksheets one by one in Excel
Unhide all hidden worksheets by VBA code
Unhide all hidden worksheets by Toggle Hidden Worksheets Visibility feature
Unhide all very hidden worksheets by Kutools for Excel
Unhide all hidden worksheets one by one in Excel
We can apply the Unhide Sheet feature to unhide a hidden worksheet at a time in Excel. Please do as follows:
3. Then the selected hidden sheet is displayed. Repeat above Step 2 to show all unhide worksheets one by one.
Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!
Unhide all hidden worksheets by VBA code
The following short VBA code also can help you display all of the hidden sheets at the same time.
1. Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
Sub UnhideAllSheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub3. Press the F5 key to run this macro. And the hidden sheets will be displayed at once.
Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!
Then all hidden sheets are shown at once. See screenshot:
Unhide all hidden worksheets by Toggle Hidden Worksheets Visibility feature
Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!
Unhide all very hidden worksheets by Kutools for Excel
Sometimes above methods cannot unhide the hidden worksheets. That’s because these worksheets are very hidden. In this situation, you can apply the Hide/Unhide Workbooks and Sheets feature of Kutools for Excel to quickly unhide them.
Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!
Now all hidden worksheets including the very hidden ones are displaying in bulk. Please close the dialog as you need.
Hide/Unhide Workbooks and Sheets hide or unhide multiple opening workbooks and their worksheets in bulk, including the very hidden ones. Have a Free Trial!
Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days.Download and Free Trial Now!
Related articles
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails…
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
How To Hide And Unhide Rows In Microsoft Excel In 2 Different Ways
10 Things in Tech: Get the latest tech trends & innovations
Loading Something is loading.
Email address
Check out the products mentioned in this article:
Marissa Perino/Business Insider
Highlight adjacent cells to unhide a hidden row.
Marissa Perino/Business Insider
Unhide all rows.
Marissa Perino/Business Insider
Related coverage from How To Do Everything: Tech:
Cập nhật thông tin chi tiết về 3 Ways To Unhide Multiple Sheets In Excel + Vba Macros trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!