Save content
Have you found this content useful? Use the button above to save it to your profile.
Business woman
istock_Yuri_Arcurs

Excel tip: Unhide several worksheets at once

by
19th May 2016
Save content
Have you found this content useful? Use the button above to save it to your profile.

Are you tired of manually unhiding worksheets one at a time? Excel trainer David Ringstrom is here to relieve you from this tedious task.  

Sometimes the simplest navigational niggles in Excel can cause the most distress. In a recent post on technology David Ringstrom revealed an Excel tip on AccountingWEB.com (our us sister site) that could save hours of repetitive stress by banishing multiple maunal unhides with a single line of VBA programming code.

Not only is unhiding multiple worksheets manually frustrating, but right-clicking on the worksheet and selecting unhide or unhiding each one through Excel’s home menu takes a lot of time.

Instead, you can circumvent this hassle with a string of VBA that instantly displays all the worksheets.

To speed up the process, hit Alt-F11 on your keyboard to display Excel’s Visual Basic Editor, and then choose the Immediate Window from the view menu.

This magical mechanism will enforce any code you tap into space immediately. Now, type this programming code into the Immediate window and then press enter:

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

After, you should see all your worksheets populate the workbook. That’s it. You will only encounter an error code if you incorrectly type in the code or if the protect workbook command is activated in Excel’s review menu. If you’ve not encountered any issues, you can exit the Visual Basic Editor.

You should now see all the worksheets in your Excel file. So, how did Ringstrom do it?

Unlike a magician, Ringstrom reveals his secret. Here's the theory behind his macro:

  • 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

Follow Ringstrom's visual guide below for further reference:

David Ringstrom

You can find this tip in full, and a collection of Ringstrom's other Excel tips on our sister site AccountingWEB.com. 

Tags:

Replies (1)

Please login or register to join the discussion.

avatar
By Brend201
20th May 2016 16:51

I like this. I needed it. It works nicely.

Thank you.

Thanks (1)