How To Unhide Sheets In Excel (All In One Go)

--- Bài mới hơn ---

  • How To Unhide All Worksheets & Sheets In Excel?
  • 3 Ways To Unhide Multiple Sheets In Excel + Vba Macros
  • Unhiding All Worksheets Within An Excel Workbook
  • Hướng Dẫn Bảo Vệ/khóa Cột Ẩn Trong Excel
  • How To Protect / Lock Hidden Columns In Excel?
  • In case you pfer reading a tutorial over watching a video, below is a detailed written tutorial on unhiding sheets in Excel.

    When you work with data that is spad across multiple worksheets in Excel, you may want to hide a few worksheets. This could be to avoid the clutter or to not show some data to your client/manager by hiding some worksheets and only keeping the useful ones visible.

    And in some cases, you may have a workbook that has some hidden sheets and you want to unhide some or all of these worksheets.

    In this tutorial, I will show you some methods to unhide worksheets in Excel (manually as well as automatically using VBA). I will also show you how to selectively unhide worksheets based on the name or a condition.

    So let’s get started!

    Unhiding Sheets Manually

    If you only have a few worksheets that are hidden, you can manually unhide some or all of these worksheets.

    Suppose you have an Excel workbook that has 10 worksheets that are hidden.

    Below are the steps to manually unhide worksheets (one at a time):

    The above steps would unhide the select worksheet.

    Note: Unfortunately, there is no in-built functionality in Excel to quickly unhide all the hidden worksheets (or a way to select more than one worksheet and unhide it). As of now, you need to use the unhide dialog box where you can only select one worksheet to unhide.

    Unfortunately, there is no in-built functionality in Excel to quickly unhide all the hidden worksheets (or a way to select more than one worksheet and unhide it). As of now, you need to use the unhide dialog box where you can only select one worksheet to unhide.

    While there is no-inbuilt functionality to unhide in bulk, you can easily do this with a simple VBA macro code.

    Unhide All Sheets At One Go

    With VBA, you can easily unhide worksheets in bulk.

    For example, if you have 10 hidden worksheets, you can create a simple VBA code to unhide all the worksheets or you can unhide based on a condition (such as unhide only those where there is a specific pfix or year in the name).

    Note: The methods covered in this tutorial doesn’t require you to save an Excel workbook in a macro-enabled format (.XLSM) to use the VBA code.

    Using Immediate Window

    VB Editor in Excel has an immediate window where you can type a line of code and instantly execute it right away.

    Below are the steps to use this above line of code to unhide sheets through immediate window:

    1. In the Immediate window, copy and paste the following line of code: For each Sheet in Thisworkbook.Sheets: Sheet.Visible=True: Next Sheet
    2. Place the cursor at the end of the line
    3. Hit the Enter key

    That’s it!

    The above steps would instantly unhide all the sheets in the workbook.

    Once done, you can close the VB Editor.

    The best part about this is that you can do this on any workbook. You don’t need to worry about saving the workbook in a macro-enabled format. Just execute a line of code and instantly unhide all the sheets in the workbook.

    Let me also quickly explain the below VBA code that we have used in the immediate window to unhide sheets:

    For each Sheet in Thisworkbook.Sheets: Sheet.Visible=True: Next Sheet

    The above code uses a For Next VBA loop to go through all the sheets in the workbook and set the visible property to TRUE. Once the visible property of all the sheets is changed, the code will end.

    The colon (:) used in the code above is equivalent to a line break. While it looks like a single line of code, it has three parts to it which are separated by two colons.

    If you’re interested in learning more about the immediate window and some awesome things you can do with it, here is a detailed tutorial about it.

    In case you have to unhide worksheets quite often, another good way could be to have the macro code to unhide sheets in the Personal macro workbook and save the icon in the Quick Access Toolbar.

    This is by far the most efficient way to unhide sheets in Excel (most useful when you get a lot of workbooks with hidden sheets and you have to unhide these).

    The trick here is to save the code to unhide sheets in the Personal Macro Workbook.

    Below is the code that you need to add to the Personal Macro Workbook:

    Sub UnhideAllSheets() For Each Sheet In Sheets Sheet.Visible = True Next Sheet End Sub

    Below are the steps to add this code to the Personal Macro Workbook:

    1. In the Record Macro dialog box, change the Store macro in setting to – Personal Macro Workbook.
    2. Remove any existing code and copy and paste the above code.
    3. Close the Vb Editor

    The above steps allow you to make the Personal Macro Workbook visible in the VB Editor and place the code to unhide sheets in it.

    Now all you need to do is add this code to the Quick Access Toolbar so that you can use it anytime from any workbook.

    Below are the steps to add this code to the Quick Access Toolbar:

    1. Select the macro code to unhide sheets

    The above steps would add this macro code to unhide sheets in the Quick Access Toolbar.

    Unhide Sheets With Specific Text in the Name

    With VBA, you can also unhide sheets based on the name.

    For example, suppose you have a workbook that contains sheets with years in the name and you want to unhide all the ones where the year is 2022.

    You can use the below code to unhide all the sheets with the text 2022 in it:

    Sub UnhideSheetsWithSpecificText() For Each ws In ThisWorkbook.Worksheets ws.Visible = xlSheetVisible End If Next ws End Sub

    The above uses the For Next loop to go through each worksheet in the workbook. The IF Then condition then checks the name of the worksheet and if it contains the specified text (which is 2022 in this code), it will change the visible property to make it visible.

    And if the name doesn’t contain the specified text, it will leave it as is.

    You can also modify this code to hide sheets based on the text in the name.

    For example, if you want to quickly hide all the worksheets where the name contains the text ‘2020’ in it, you can use the below code:

    Sub HideSheetsWithSpecificText() For Each ws In ThisWorkbook.Worksheets ws.Visible = xlHidden End If Next ws End Sub

    Note: You can save this code in a regular module inside VB Editor or you can save this in the Personal Macro Workbook. In case you save it in a regular module and need to use it again later, you need to save the workbook as a macro-enabled workbook (.XLSM format).

    Unhide Selected Sheets (Based on User Selection)

    You can also use VBA to give the user the flexibility to choose whether to unhide a sheet or not.

    This can be done by showing a message box that asks the user to select whether to unhide a sheet or not. If selected, it unhides that sheet, else it moves to the next one.

    Below is the code that will do this:

    Sub UnhideSheetsUserSelection() For Each sh In ThisWorkbook.Sheets Result = MsgBox("Do You Want to Unhide " & sh.Name, vbYesNo) If Result = vbYes Then sh.Visible = True End If Next sh End Sub

    The above code goes through each sheet in the workbook and checks whether it’s already visible or not. If it’s hidden, then it shows the message box with the name of the worksheet.

    As a user, you can now decide whether you want to keep this sheet hidden or unhide it.

    This can work well if you have some worksheets that are hidden and you want to take a call for every sheet inpidually.

    Note: You can save this code in a regular module inside VB Editor or you can save this in the Personal Macro Workbook. In case you save it in a regular module and need to use it again later, you need to save the workbook as a macro-enabled workbook (.XLSM format).

    Here is a tutorial where I show how to save the code in the regular module in Excel (search for the ‘Where to put this code’ section in this article)

    Unhide All or Selected Sheets Using Custom View

    This is a less known method in case you want to quickly unhide all the worksheets (or some selected worksheets).

    For example, suppose you have an Excel workbook with 10 worksheets. You can create a view where all these 10 sheets are visible. In the future, if you have some sheets hidden and you want o go back to the view where all the sheets were visible, you can do that by selecting the already saved custom view.

    Don’t worry, you don’t lose any changes you made after creating the custom view. All custom view does is takes you back to the Excel view when you created it. So if some worksheets were visible when you created the view and are now hidden, selecting that custom view would unhide these sheets.

    The intended use of Custom View is to allow users to create different views. For example, if you’re an analyst, you can create different views for different departments in your organization. So you can have a specific set of worksheets (or cells/rows/columns) visible for one department and another set for another department. Once you have these views, instead of changing this manually, you simply activate the view for a department and it will show you worksheets (or rows/columns) relevant for them only.

    Below are the steps to create a custom view in Excel:

    1. Unhide all the worksheets to begin with
    2. Enter any name for this view where all the sheets (or selected sheets) are visible

    Once the view is created, you can anytime ask Excel to activate this view (which would make all those sheets visible that were visible when you created the view).

    Below are the steps to show/activate a custom view:

    1. In the Custom Views dialog box, select the view that you want to show

    This would instantly unhide sheets and show those that were visible when you created that custom view.

    Unhiding Sheets that are ‘Very Hidden’

    Sometimes, despite having some hidden sheets in your workbook, you would not be able to unhide it manually.

    This could be because these sheets are not just hidden – these are ‘very hidden’.

    You can still unhide these ‘very hidden’ sheets by using the VBA code that we have covered above.

    Just copy-paste the below code in the immediate window and hit enter and it would instantly unhide all the sheets (hidden as well as very hidden).

    For each Sheet in Thisworkbook.Sheets: Sheet.Visible=True: Next Sheet

    I also have a full tutorial on how to hide sheets and make these very hidden (in case you’re interested in learning)

    You may also like the following Excel tutorials:

    --- Bài cũ hơn ---

  • Hướng Dẫn Công Thức Tính Ngày Công Trong Excel
  • Làm Cách Nào Để Hiển Thị Tỷ Lệ Phần Trăm Trong Biểu Đồ Cột Xếp Chồng Trong Excel?
  • Ghi Nhớ Nhóm Hàm Thống Kê Trong Excel Cùng Sửa Máy Tính Tại Nhà
  • Mẫu Báo Cáo Doanh Thu Trên Excel
  • Cách Quản Lý Bán Hàng Bằng Excel Đơn Giản Và Hiệu Quả
  • How To Unhide All Worksheets & Sheets In Excel?

    --- Bài mới hơn ---

  • 3 Ways To Unhide Multiple Sheets In Excel + Vba Macros
  • Unhiding All Worksheets Within An Excel Workbook
  • Hướng Dẫn Bảo Vệ/khóa Cột Ẩn Trong Excel
  • How To Protect / Lock Hidden Columns In Excel?
  • How To Quickly Unhide Columns 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.

    1. Unhide all hidden worksheets one by one in Excel
    2. Unhide all hidden worksheets by VBA code
    3. Unhide all hidden worksheets by Toggle Hidden Worksheets Visibility feature
    4. 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 Sub

    3. 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.

    --- Bài cũ hơn ---

  • How To Unhide Sheets In Excel (All In One Go)
  • Hướng Dẫn Công Thức Tính Ngày Công Trong Excel
  • Làm Cách Nào Để Hiển Thị Tỷ Lệ Phần Trăm Trong Biểu Đồ Cột Xếp Chồng Trong Excel?
  • Ghi Nhớ Nhóm Hàm Thống Kê Trong Excel Cùng Sửa Máy Tính Tại Nhà
  • Mẫu Báo Cáo Doanh Thu Trên Excel
  • How To Unhide Sheets In Excel: Show Multiple Or All Sheets At Once

    --- Bài mới hơn ---

  • Thủ Thuật Unhide All Sheet Trong Excel Siêu Đơn Giản
  • How To Unhide Rows In Excel
  • How To Hide And Unhide Rows In Excel
  • Phím Tắt Cad, Tổng Hợp Lệnh Tắt Trong Autocad
  • Cách Chuyển Chữ Thường Thành Chữ Hoa Trong Word Excel 2022
  • 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 inpidually 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 inpidually, 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:

    1. Open the workbook with hidden sheets.
    2. Press Alt + F11 to open the Visual Basic Editor.
    3. Paste the code in the Code window.
    4. 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:

    1. Open the downloaded workbook and enable the macros if prompted.
    2. 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

    --- Bài cũ hơn ---

  • 8 Hàm Ngày Tháng Thường Dùng Trong Nghiệp Vụ Tính Lương
  • Cách Tính Bảng Chấm Công Trong Excel Được Thực Hiện Như Thế Nào?
  • Sắp Xếp Tên Theo Thứ Tự Abc Trong Excel
  • Tạo Mục Lục Cho Sheet Trong Excel Và Cách Tạo Liên Kết Các Sheet
  • Hàm Forecast.ets.stat Trong Excel, Trả Về Giá Trị Thống Kê Được Chỉ Định
  • A Macro To Unhide All Hidden Sheets In An Excel Workbook

    --- Bài mới hơn ---

  • How To Hide And Unhide Worksheets In Excel
  • Khóa Cột Ẩn Trong Excel
  • How To Hide And Unhide Rows In Microsoft Excel In 2 Different Ways
  • Unhide All Rows / Columns
  • How To Group And Ungroup Worksheets In Excel
  • 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.

    --- Bài cũ hơn ---

  • Hướng Dẫn Tính Tổng Doanh Số Theo Từng Tháng Trong Excel
  • Nhóm Hàm Thống Kê Trong Excel: Cách Sử Dụng Và Ví Dụ Minh Họa (Phần 1)
  • Tổng Hợp Mẫu File Excel Quản Lý Bán Hàng Online 2022
  • Làm Cách Nào Để Cộng Hoặc Trừ Thời Gian Để Có Được Giờ
  • Cách Cộng, Trừ Ngày Tháng Năm Trong Excel Để Tính Số Ngày Đơn Giản
  • How To Unhide Sheets In Excel: Show Multiple Or All Hidden Sheets At A Time

    --- Bài mới hơn ---

  • Cách Sử Dụng Phím Tắt Khi Paste Value Trong Excel
  • 5 Keyboard Shortcuts To Paste Values In Excel
  • Top 100 Mã Excel Vba Macros Hữu Dụng
  • Các Phím Tắt Trong Microsoft Word Và Microsoft Excel
  • Cách Chèn Bảng Excel Vào Word 2007, 2010, 2013
  • 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 inpidually 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 inpidually, 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:

    1. Open the workbook with hidden sheets.
    2. Press

      Alt + F11

      to open the Visual Basic Editor.

    3. Paste the code in the Code window.
    4. 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:

    1. Open the downloaded workbook and enable the macros if prompted.
    2. 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.

    How to check if a workbook contains any hidden sheets

    Note. This method does not show

    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

    --- Bài cũ hơn ---

  • Cách Ẩn Cột, Ẩn Hàng Trong Excel 2022, 2013, 2010
  • – Cách Tách Họ Và Tên Trong Excel
  • Hướng Dẫn Hàm Int() Và Hàm Mod() Trong Excel
  • Hàm Subtotal Trong Excel – Cách Dùng Hàm Subtotal Qua Ví Dụ
  • Cách Sử Dụng Consolidate Và Subtotal Để Thống Kê Dữ Liệu Trong Excel
  • Thủ Thuật Unhide All Sheet Trong Excel Siêu Đơn Giản

    --- Bài mới hơn ---

  • How To Unhide Rows In Excel
  • How To Hide And Unhide Rows In Excel
  • Phím Tắt Cad, Tổng Hợp Lệnh Tắt Trong Autocad
  • Cách Chuyển Chữ Thường Thành Chữ Hoa Trong Word Excel 2022
  • 2 Cách Chuyển Chữ Thường Thành Chữ Hoa Trong Excel
  • Unhide all sheet trong Excel là gì?

    Unhide all sheet trong Excel chính là việc người dùng bỏ ẩn toàn bộ các sheet trong Excel. Điều này có nghĩa là khi người dùng đã ẩn đi các sheet trong Excel. Tuy nhiên, đến một lúc nào đó bạn cần hiển thị lại những sheet này.

    Hướng dẫn bỏ ẩn toàn bộ các sheet trong Excel

    Chúng ta sẽ bỏ qua cách bỏ ẩn cho từng sheet trong Excel một. Bởi vì, cách làm này sẽ ngốn rất nhiều thời gian của bạn. Chúng tôi sẽ mách bạn cách unhide all sheet trong Excel một cách thông minh hơn. Đó là:

    Bước 1: Khởi động Excel

    Bước 2: Bỏ ẩn toàn bộ sheet trong Excel

    Trong giao diện cửa sổ soạn thảo của Excel, bạn nhấn tổ hợp phím Alt + F11. Sau đó, trên thanh công cụ bạn sẽ nhìn thấy mục Insert và hãy kích vào đó. Cuối cùng, trong Insert bạn sẽ chọn vào Module.

    Bước 3: Code ẩn toàn bộ sheet trong Excel

    Sub Unhide_AllSheet() ‘Bỏ ẩn tất cả các Sheet

    Dim ws As Worksheet On Error Resume Next ‘Bỏ qua các lỗi có thể xảy ra ‘Bỏ ẩn các sheet For Each ws In Sheets ws.Visible=True Next ‘Vô hiệu hóa bất kỳ lỗi nào xảy ra On Error Goto 0

    Bước 4: Chạy code

    Sau khi bạn copy và paste xong đoạn code trên vào cửa sổ soạn thảo. Lúc này, bạn sẽ tiến hành chạy đoạn code xem đã chính xác chưa bằng 2 cách. Đó là:

    • Cách 1: Bạn sẽ nhấn F5 để chạy code.
    • Cách 2: Bạn nhấn vào nút tam giác có màu xanh trên menu của cửa sổ soạn thảo.

    Nếu code chính xác, toàn bộ các sheet trong Excel đã được hiển thị lên. Như vậy là bạn đã thành công với cách unhide all sheet trong excel rồi đó.

    Hướng dẫn ẩn toàn bộ các sheet trong Excel

    Cũng giống với việc bạn unhide all sheet trong Excel. Đối với hành động ẩn đi toàn bộ sheet bạn cũng không nên thực hiện riêng rẽ. Thay vào đó, bạn hãy áp dụng cách mà chúng tôi hướng dẫn như sau:

    • Đầu tiên, bạn sẽ kích chuột vào 1 sheet nào đó trong Excel. Sau đó, bạn sẽ kích vào phím Ctrl trên bàn phím máy tính. Tại đây, bạn tiếp tục chọn ra những sheet cần ẩn hoặc toàn bộ các sheet.
    • Sau khi chọn xong các sheet cần ẩn bạn sẽ kích chuột phải rồi chọn hide.

    Như vậy, rất nhanh chóng toàn bộ các sheet bạn chọn đã bị ẩn đi. Đối với cách ẩn toàn bộ các sheet trong Excel này đơn giản hơn rất nhiều. Nhất là khi bạn đi ẩn từng sheet một trong Excel vừa lâu lại mất thời gian.

    --- Bài cũ hơn ---

  • How To Unhide Sheets In Excel: Show Multiple Or All Sheets At Once
  • 8 Hàm Ngày Tháng Thường Dùng Trong Nghiệp Vụ Tính Lương
  • Cách Tính Bảng Chấm Công Trong Excel Được Thực Hiện Như Thế Nào?
  • Sắp Xếp Tên Theo Thứ Tự Abc Trong Excel
  • Tạo Mục Lục Cho Sheet Trong Excel Và Cách Tạo Liên Kết Các Sheet
  • Unhiding All Worksheets Within An Excel Workbook

    --- Bài mới hơn ---

  • Hướng Dẫn Bảo Vệ/khóa Cột Ẩn Trong Excel
  • How To Protect / Lock Hidden Columns In Excel?
  • How To Quickly Unhide Columns In Excel
  • Cánh Ẩn Hoàn Toàn Và Hiện Sheets Trong Workbook
  • Hướng Dẫn Thêm, Xoá, Ẩn, Hiện Cột Trong Excel 2022
  • Although you can quickly hide as many worksheets within a workbook as you like, we’re still limited to unhiding inpidual worksheets one at a time – unless you’re aware of Excel’s Custom Views feature. Fortunately, there is another way to avoid the agony of manually unhiding worksheets one at a time.

    In this article I’ll show you how to use a single line of programming code to unhide the worksheets. Programming code in Excel is often referred to as macros. In this case we’re not creating a permanent macro, but rather typing a line of code to run on demand.

    Figure 1: There are a variety of ways to hide worksheets in Excel.

    Figure 2: Unfortunately, you must unhide worksheets one at a time.

    You probably don’t have the time or inclination to unhide more than a couple of worksheets in this fashion, so instead we’ll use a bit of programming code to instantly display all worksheets at once:

    1. As illustrated in Figure 3, pss Alt-F11 on your keyboard to display Excel’s Visual Basic Editor. Mac users should pss Fn-Alt-F11. Although it looks like a separate program, it’s a hidden aspect of Excel that most users haven’t seen before.
    2. Select Immediate Window from the View menu, or pss Ctrl-G on your keyboard (for Mac, Ctrl-Cmd-G).
    3. At this point the Immediate window will appear on-screen. This is a special area where any programming code you type will be executed immediately, hence the name.
    4. Type the following line of programming code into the Immediate window exactly as written below, and pss Enter.

    For Each s In Sheets: s.Visible = True: Next

    The downside of the Immediate Window is you don’t get any direct feedback if your programming code worked, other than seeing that all of your worksheets are now visible within the workbook. Error prompts will appear if you pss Enter when the line of code is either incomplete or contains typographical errors.

    You may also encounter an error if the workbook is protected by way of the Protect Workbook command on Excel’s Review menu.

      You can safely exit the Visual Basic Editor once you’ve run the line of code.

    Figure 3: A single line of code in the Immediate Window will unhide all worksheets in the workbook.

    The aforementioned line of code utilizes Visual Basic for Applications in Microsoft Excel. This is known as an object-oriented programming language, so if you want a little insight as to what the macro is doing:

    • For Each sets up a loop.
    • s is a variable that serves as a temporary placeholder for a worksheet to be acted on.
    • Sheets is a collection of all worksheets within the workbook. This actually includes other types of sheets as well, meaning Chart Sheets and Macro Worksheets. We could be more specific and use the Worksheets collection instead, but Sheets results in less typing.
    • Each worksheet has a Visible property, and in this case we’re setting it to True. The setting gets set to False when you hide a worksheet.
    • Next simply instructs Excel to skip to the next worksheet in succession, until all have been processed.

    If you were to store this within a formal macro, the code might take this form:

    For each s in Sheets

    s.Visible

    Next

    The Immediate Window only allows us to execute a single line of code at a time, so the colons allow us to string three lines of code together into a single line that can be executed.

    --- Bài cũ hơn ---

  • 3 Ways To Unhide Multiple Sheets In Excel + Vba Macros
  • How To Unhide All Worksheets & Sheets In Excel?
  • How To Unhide Sheets In Excel (All In One Go)
  • Hướng Dẫn Công Thức Tính Ngày Công Trong Excel
  • Làm Cách Nào Để Hiển Thị Tỷ Lệ Phần Trăm Trong Biểu Đồ Cột Xếp Chồng Trong Excel?
  • Unhide All Rows / Columns

    --- Bài mới hơn ---

  • How To Group And Ungroup Worksheets In Excel
  • How To Group Or Ungroup Worksheets In Excel?
  • Top 5 Ways To Undo Last Action Quickly
  • 【1️⃣】 Cách Tăng Số Lần Undo Trong Word, Excel, Powerpoint
  • Excel Paste Special: Shortcuts To Copy Values, Comments, Column Width, Etc.
  • This tutorial will demonstrate how to unhide all rows and / or columns in an Excel worksheet using VBA.

    Unhide All Rows

    To unhide all rows in an Excel sheet, we will set the Hidden Property of all of the rows to FALSE.

    We can access all rows by using the EntireRow Property of the Cells Object:

    1

    Cells

    .

    EntireRow

    .

    Hidden

    =

    False

    or by using the EntireRow Property of the Rows Object:

    1

    Rows

    .

    EntireRow

    .

    Hidden

    =

    False

    Unhide All Columns

    Similarily, we can unhide all columns in an Excel sheet, by adjusting the Hidden Property of all the Columns.

    You can access all of the columns by using the EntireColumn Property of the Cells Object:

    1

    Cells

    .

    EntireColumn

    .

    Hidden

    =

    False

    or by using the EntireColumn Property of the Columns Object:

    1

    Columns

    .

    EntireColumn

    .

    Hidden

    =

    False

    Hide All Rows or Columns

    Of course, to hide all rows or columns, just set the Hidden Property to TRUE:

    1

    Columns

    .

    EntireColumn

    .

    Hidden

    =

    True

    Macro to Unhide All Rows and Columns

    Use this macro to unhide all rows and columns in a worksheet:

    1

    2

    3

    4

    Sub

    Unhide_All_Rows_Columns

    (

    )

        

    Columns

    .

    EntireColumn

    .

    Hidden

    =

    False

        

    Rows

    .

    EntireRow

    .

    Hidden

    =

    False

    End

    Sub

    Macro to Unhide All Rows and Columns on all Sheets

    This macro will unhide all rows and columns in all sheets in an Excel workbook:

    1

    2

    3

    4

    5

    6

    7

    8

    Sub

    Unhide_All_Rows_Columns_in_Workbook

    (

    )

        

    Dim

    ws

    As

    Worksheet

        

        

    For

    Each

    ws

    In

    Worksheets

            

    Columns

    .

    EntireColumn

    .

    Hidden

    =

    False

            

    Rows

    .

    EntireRow

    .

    Hidden

    =

    False

        

    Next

    ws

    End

    Sub

     

    --- Bài cũ hơn ---

  • How To Hide And Unhide Rows In Microsoft Excel In 2 Different Ways
  • Khóa Cột Ẩn Trong Excel
  • How To Hide And Unhide Worksheets In Excel
  • A Macro To Unhide All Hidden Sheets In An Excel Workbook
  • Hướng Dẫn Tính Tổng Doanh Số Theo Từng Tháng Trong Excel
  • 3 Ways To Unhide Multiple Sheets In Excel + Vba Macros

    --- Bài mới hơn ---

  • Unhiding All Worksheets Within An Excel Workbook
  • Hướng Dẫn Bảo Vệ/khóa Cột Ẩn Trong Excel
  • How To Protect / Lock Hidden Columns In Excel?
  • How To Quickly Unhide Columns In Excel
  • Cánh Ẩn Hoàn Toàn Và Hiện Sheets Trong Workbook
  • 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:

    1. Alt+F11 (opens the VB Editor Window)
    2. Ctrl+G (opens the Immediate Window)
    3. Paste the following line of code in the Immediate Window and pss Enter

      For 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 Sub

    Download 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” repsent 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 Sub

    Download 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!) 🙂

    --- Bài cũ hơn ---

  • How To Unhide All Worksheets & Sheets In Excel?
  • How To Unhide Sheets In Excel (All In One Go)
  • Hướng Dẫn Công Thức Tính Ngày Công Trong Excel
  • Làm Cách Nào Để Hiển Thị Tỷ Lệ Phần Trăm Trong Biểu Đồ Cột Xếp Chồng Trong Excel?
  • Ghi Nhớ Nhóm Hàm Thống Kê Trong Excel Cùng Sửa Máy Tính Tại Nhà
  • How To Unhide Rows In Excel

    --- Bài mới hơn ---

  • How To Hide And Unhide Rows In Excel
  • Phím Tắt Cad, Tổng Hợp Lệnh Tắt Trong Autocad
  • Cách Chuyển Chữ Thường Thành Chữ Hoa Trong Word Excel 2022
  • 2 Cách Chuyển Chữ Thường Thành Chữ Hoa Trong Excel
  • Cách Chuyển Chữ Thường Thành Chữ Hoa Và Ngược Lại
  • Download Article

    Download Article

    Unhiding a Specific Row

    1. Find the hidden row. Look at the row numbers on the left side of the document as you scroll down; if you see a skip in numbers (e.g., row 23 is directly above row 25), the row in between the numbers is hidden (in 23 and 25 example, row 24 would be hidden). You should also see a double line between the two row numbers.

    2. It’s in the drop-down menu. Doing so will prompt the hidden row to appear.

    3. Unhide a range of rows. If you notice that several rows are missing, you can unhide all of the rows by doing the following:

    1. This tab is just below the green ribbon at the top of the Excel window.

        If you’re already on the Home tab, skip this step.
    2. This option is in the “Cells” section of the toolbar near the top-right of the Excel window. A drop-down menu will appear.

    3. You’ll find this option in the Format drop-down menu. Selecting it prompts a pop-out menu to appear.

    Adjusting Row Height

    1. This tab is just below the green ribbon at the top of the Excel window.

        If you’re already on the Home tab, skip this step.
    2. This option is in the “Cells” section of the toolbar near the top-right of the Excel window. A drop-down menu will appear.

    3. It’s in the drop-down menu. This will open a pop-up window with a blank text field in it.

    4. Enter the default row height. Type 14.4 into the pop-up window’s text field.

    Community Q&A

    Add New Question

    • The top 7 rows of my Excel worksheet have disappeared. I’ve tried to “unhide” from the Format menu, but nothing happens. What do I do?

      You’ll have to unlock the cells (via the format pop-up), then hide them all before unhiding them.

    • There is a possibility you did not hide the rows but reduced your rows’ height to minimum. Select all rows above and below of your 7 rows and increase rows height from format menu. It will re-adjust the height of rows and your rows will be visible.

    This article was written by Jack Lloyd. Jack Lloyd is a Technology Writer and Editor for wikiHow. He has over two years of experience writing and editing technology-related articles. He is technology enthusiast and an English teacher. This article has been viewed 309,392 times.

    How helpful is this?

    Updated: November 17, 2022

    Thanks to all authors for creating a page that has been read 309,392 times.

      Robert Theriault

      “I had some rows hidden and couldn’t p out how to unhide the rows. The article solved that problem for me. Thanks” …” more

    --- Bài cũ hơn ---

  • Thủ Thuật Unhide All Sheet Trong Excel Siêu Đơn Giản
  • How To Unhide Sheets In Excel: Show Multiple Or All Sheets At Once
  • 8 Hàm Ngày Tháng Thường Dùng Trong Nghiệp Vụ Tính Lương
  • Cách Tính Bảng Chấm Công Trong Excel Được Thực Hiện Như Thế Nào?
  • Sắp Xếp Tên Theo Thứ Tự Abc Trong Excel
  • Web hay
  • Links hay
  • Push
  • Chủ đề top 10
  • Chủ đề top 20
  • Chủ đề top 30
  • Chủ đề top 40
  • Chủ đề top 50
  • Chủ đề top 60
  • Chủ đề top 70
  • Chủ đề top 80
  • Chủ đề top 90
  • Chủ đề top 100
  • Bài viết top 10
  • Bài viết top 20
  • Bài viết top 30
  • Bài viết top 40
  • Bài viết top 50
  • Bài viết top 60
  • Bài viết top 70
  • Bài viết top 80
  • Bài viết top 90
  • Bài viết top 100