When you choose to hide worksheets in a workbook, Excel allows you to hide multiple sheets with one action: all you need to do is select the sheets before actually doing the hiding. Unhiding worksheets is a different story, however. Excel only allows you to unhide one at a time. If you have many worksheets you want to unhide, this can be very tedious.
The only way around this is to use a macro to unhide the worksheets. The following VBA macro will unhide all the worksheets in the current workbook:
Sub UnhideAllSheets() Dim wsSheet As Worksheet For Each wsSheet In ActiveWorkbook.Worksheets wsSheet.Visible = xlSheetVisible Next wsSheet End Sub
If you would rather not unhide all the worksheets at once, you can cause the macro to ask about each hidden worksheet and then unhide each that you agree to unhide. The following macro will handle this task:
Sub UnhideSomeSheets() Dim sSheetName As String Dim sMessage As String Dim Msgres As VbMsgBoxResult For Each wsSheet In ActiveWorkbook.Worksheets If wsSheet.Visible = xlSheetHidden Then sSheetName = wsSheet.Name sMessage = "Unhide the following sheet?" _ & vbNewLine & sSheetName Msgres = MsgBox(sMessage, vbYesNo) If Msgres = vbYes Then wsSheet.Visible = xlSheetVisible End If Next wsSheet End Sub
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9636) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Unhiding Multiple Worksheets.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Excel allows you to "freeze" rows in your worksheet. What if you want the rows that are frozen to change as you scroll ...
Discover MoreWorksheets are easily accessible in a workbook, but you may not want them to be so open. You can hide worksheets so they ...
Discover MoreIf you have a lot of worksheets in a workbook, you may wonder if you can "freeze" the position of some of those worksheet ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-12-14 09:38:34
Roy Taylor
I have found the free "Excel Utilities" that you prompted in one of your other tips a useful aid. APSPRO.COM
It has a button that does what this tip is all about and unhides all hidden sheets.
There are lots of other features that I still have to investigate but for this feature alone it is worth the down load.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments