{"id":2789,"date":"2020-12-15T14:59:51","date_gmt":"2020-12-15T22:59:51","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=2789"},"modified":"2020-12-15T15:13:38","modified_gmt":"2020-12-15T23:13:38","slug":"how-to-unhide-multiple-sheets-in-excel-at-once","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=2789","title":{"rendered":"How to Unhide Multiple Sheets in Excel at Once"},"content":{"rendered":"\n<p><em>The tutorial explains how to unhide worksheets in Excel 2016, 2013, 2010 and lower. You will learn how to quickly unhide worksheet by right-clicking and how to unhide all sheets at a time with VBA code.<\/em><\/p>\n\n\n\n<p>Imagine this: you open a worksheet and notice that some formulas refer to another worksheet. You look at the sheet tabs, but the referenced spreadsheet is not there! You try to create a new sheet with the same name, but Excel tells you it already exists. What does that all mean? Simply, the worksheet is hidden. How to view hidden sheets in Excel? Obviously, you have to unhide them. This can be done manually by using Excel&#8217;s&nbsp;<em>Unhide<\/em>&nbsp;command or automatically with VBA. This tutorial will teach you both methods.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"unhide-sheets-manually\">How to unhide sheets in Excel<\/h2>\n\n\n\n<p>If you want to see just one or two hidden sheets, here&#8217;s how you can quickly unhide them:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>In your Excel workbook, right-click any sheet tab and select&nbsp;<strong>Unhide<\/strong>\u2026 from the context menu.<\/li><li>In the&nbsp;<em>Unhide<\/em>&nbsp;box, select the hidden sheet you want to display and click&nbsp;<em>OK<\/em>&nbsp;(or double-click the sheet name). Done!<\/li><\/ol>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/unhide-sheets\/unhide-sheets-excel.png\" alt=\"Unhide sheets in Excel via the right-click menu.\" title=\"Unhide sheets in Excel via the right-click menu.\"\/><\/figure>\n\n\n\n<p>Besides the right-click contextual menu, the&nbsp;<em>Unhide<\/em>&nbsp;dialog can be accessed from the ribbon:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>In Excel 2003 and earlier, click the&nbsp;<em>Format<\/em>&nbsp;menu, and then click&nbsp;<em>Sheet<\/em>&nbsp;&gt;&nbsp;<strong>Unhide<\/strong>.<\/li><li>In Excel 2016, Excel 2013, Excel 2010 and Excel 2007, go to the&nbsp;<em>Home<\/em>&nbsp;tab &gt;&nbsp;<em>Cells<\/em>&nbsp;group, and click the&nbsp;<em>Format<\/em>&nbsp;Under&nbsp;<em>Visibility<\/em>, point to&nbsp;<em>Hide &amp; Unhide<\/em>, and then click&nbsp;<strong>Unhide Sheet<\/strong>\u2026<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/unhide-sheets\/excel-unhide-sheets-ribbon.png\" alt=\"Unhide worksheet using the ribbon.\" title=\"Unhide worksheet using the ribbon.\"\/><\/figure>\n\n\n\n<p><strong>Note.<\/strong>&nbsp;Excel&#8217;s&nbsp;<em>Unhide<\/em>&nbsp;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 individually or you can unhide all sheets in one go by using the below macros.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"unhide-sheets-VBA\">How to unhide sheets in Excel with VBA<\/h2>\n\n\n\n<p>In situations when you have multiple hidden worksheets, unhiding them one-by-one might be very time consuming, especially if you&#8217;d like to unhide all the sheets in your workbook. Fortunately, you can automate the process with one of the following macros.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"unhide-all-sheets-excel\">How to unhide all sheets in Excel<\/h3>\n\n\n\n<p>This small macro makes all hidden sheets in an active workbook visible at once, without disturbing you with any notifications.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Sub Unhide_All_Sheets()\r\nDim wks As Worksheet\nFor Each wks In ActiveWorkbook.Worksheets\n    wks.Visible = xlSheetVisible\nNext wks\nEnd Sub<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Show all hidden sheets and display their count<\/h3>\n\n\n\n<p>Like the above one, this macro also displays all hidden sheets in a workbook. The difference is that upon completion, it shows a dialogue box informing the user how many sheets have been unhidden:<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/unhide-sheets\/excel-unhidden-sheets-count.png\" alt=\"Unhide all sheets in Excel with VBA\"><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Sub Unhide_All_Sheets_Count()\nDim wks As Worksheet\nDim count As Integer\ncount = 0\nFor Each wks In ActiveWorkbook.Worksheets\n    If wks.Visible &lt;&gt; xlSheetVisible Then\n        wks.Visible = xlSheetVisible\n        count = count + 1\n    End If\nNext wks\nIf count &gt; 0 Then\n    MsgBox count &amp; \" worksheets have been unhidden.\", vbOKOnly, \"Unhiding worksheets\"\nElse\n    MsgBox \"No hidden worksheets have been found.\", vbOKOnly, \"Unhiding worksheets\"\nEnd If\nEnd Sub<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"unhide-selected-worksheets\">Unhide multiple sheets that you select<\/h3>\n\n\n\n<p>If you&#8217;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 individually, like this:<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/unhide-sheets\/unhide-multiple-sheets-excel.png\" alt=\"Unhide multiple sheets with VBA\"><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Sub Unhide_Selected_Sheets()\r\nDim wks As Worksheet\r\nDim MsgResult As VbMsgBoxResult\nFor Each wks In ActiveWorkbook.Worksheets\n    If wks.Visible = xlSheetHidden Then\n        MsgResult = MsgBox(\"Unhide sheet \" &amp; wks.Name &amp; \"?\", vbYesNo, \"Unhiding worksheets\")\n        If MsgResult = vbYes Then wks.Visible = xlSheetVisible\n    End If\nNext\nEnd Sub<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"unhide-worksheets-by-name\">Unhide worksheets with a specific word in the sheet name<\/h3>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>In this example, we unhide sheets with the word &#8220;<em>report<\/em>&#8221; in the name. The macro will display sheets such as&nbsp;<em>Report<\/em>,&nbsp;<em>Report 1<\/em>,&nbsp;<em>July report<\/em>, and the like.<\/p>\n\n\n\n<p>To unhide worksheets whose names contain some other word, replace &#8220;<em>report<\/em>&#8221; in the following code with your own text.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Sub Unhide_Sheets_Contain()\nDim wks As Worksheet\nDim count As Integer\ncount = 0\nFor Each wks In ActiveWorkbook.Worksheets\n    If (wks.Visible &lt;&gt; xlSheetVisible) And (InStr(wks.Name, \"report\") &gt; 0) Then\n        wks.Visible = xlSheetVisible\n        count = count + 1\n    End If\nNext wks\nIf count &gt; 0 Then\n    MsgBox count &amp; \" worksheets have been unhidden.\", vbOKOnly, \"Unhiding worksheets\"\nElse\n    MsgBox \"No hidden worksheets with the specified name have been found.\", vbOKOnly, \"Unhiding worksheets\"\nEnd If\nEnd Sub<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">How to use the macros to unhide sheets in Excel<\/h3>\n\n\n\n<p>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.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">How to insert the macro in your workbook<\/h4>\n\n\n\n<p>You can add any of the above macros to your workbook in this way:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>Open the workbook with hidden sheets.<\/li><li>Press&nbsp;Alt + F11&nbsp;to open the Visual Basic Editor.<\/li><li>On the left pane, right-click&nbsp;<em>ThisWorkbook<\/em>&nbsp;and select&nbsp;<em>Insert<\/em>&nbsp;&gt;&nbsp;<em>Module<\/em>&nbsp;from the context menu.<\/li><li>Paste the code in the Code window.<\/li><li>Press&nbsp;F5&nbsp;to run the macro.<\/li><\/ol>\n\n\n\n<h4 class=\"wp-block-heading\">Download the workbook with the macros<\/h4>\n\n\n\n<p>Alternatively, you can download our sample workbook to&nbsp;unhide sheets in Excel&nbsp;that contains all of the macros discussed in this tutorial:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><em>Unhide_All_Sheets<\/em>&nbsp;&#8211; unhide all worksheets in an active workbook momentarily and silently.<\/li><li><em>Unhide_All_Sheets_Count\u00ad<\/em>&nbsp;&#8211; show all hidden sheets along with their count.<\/li><li><em>Unhide_Selected_Sheets<\/em>&nbsp;&#8211; display hidden sheets you choose to unhide.<\/li><li><em>Unhide_Sheets_Contain<\/em>&nbsp;&#8211; unhide worksheets whose names contain a specific word or text.<\/li><\/ul>\n\n\n\n<p>To run the macros in your Excel, you do the following:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>Open the downloaded workbook and enable the macros if prompted.<\/li><li>Open your own workbook in which you want to see hidden sheets.<\/li><li>In your workbook, press&nbsp;Alt + F8, select the desired macro, and click&nbsp;<strong>Run<\/strong>.<\/li><\/ol>\n\n\n\n<p>For example, to unhide all sheets in your Excel file and display the hidden sheets count, you run this macro:<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/unhide-sheets\/unhide-all-sheets-excel.png\" alt=\"A macro to unhide all sheets in Excel\"><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Available downloads<\/h3>\n\n\n\n<p>From the author&#8217;s website: <a href=\"https:\/\/cdn.ablebits.com\/excel-tutorials-examples\/excel-unhide-sheets.xlsm\">Macros to unhide worksheets in Excel<\/a><\/p>\n\n\n\n<p>or click &#8220;Download&#8221; to download from here<\/p>\n\n\n\n<div class=\"wp-block-file\"><a href=\"https:\/\/SUMMALAI.COM\/wp-content\/uploads\/2020\/12\/excel-unhide-sheets.7z\">excel-unhide-sheets<\/a><a href=\"https:\/\/SUMMALAI.COM\/wp-content\/uploads\/2020\/12\/excel-unhide-sheets.7z\" class=\"wp-block-file__button\" download>Download<\/a><\/div>\n\n\n\n<p>Ref: https:\/\/www.ablebits.com\/office-addins-blog\/2017\/12\/06\/unhide-sheets-excel\/<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The tutorial explains how to unhide worksheets in Excel 2016, 2013, 2010 and lower. You will learn how to quickly unhide worksheet by right-clicking and how to unhide all sheets at a time with VBA code. Imagine this: you open a worksheet and notice that some formulas refer to another worksheet. You look at the <a class=\"read-more\" href=\"https:\/\/SUMMALAI.COM\/?p=2789\">Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,18],"tags":[501,500],"class_list":["post-2789","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-microsoft-office","tag-unhide-all-sheets","tag-unhide-sheets"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/2789","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2789"}],"version-history":[{"count":6,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/2789\/revisions"}],"predecessor-version":[{"id":2801,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/2789\/revisions\/2801"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2789"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2789"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2789"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}