Unhide Multiple Worksheets At Once With A Small Piece Of VBA Code

You probably already know that you can only unhide one Excel worksheet at a time. If you did not then just test it for yourself. Try to unhide more than one of your hidden worksheets in an Excel workbook. This suits users most of the time, but it can be a little frustrating if you have a large number of hidden worksheets you want to show if you are dealing with a larger Excel workbook. In this blog post, we will look at a simple line of code to unhide multiple worksheets at once. We then will turn this code into a standalone Macro to use at a later date.

We can use the For Next Loop in our code which will loop through all of the worksheets in our active workbook and make them visible by setting the visible property to True.

Here is the line of code. (well it is a few lines really, joined with the colon character)

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

To use the code-

Open the Visual Basic Editor by hitting ALT+F11
Hit CTRL+G this opens the Immediate Window
Paste the copied code into the Immediate Window
Press Enter
The Code will run

Note: the immediate window normally only evaluates one line of code at a time, but if you put a colon: between the lines of code you can run the more than one line to get Excel to show all of the hidden worksheets. Go give it a go. Do you think you might use this?.

Let’s turn This Code Into A Reusable Excel Macro.

So that bit of code is useful right?. Well, let’s take it and make it into a reusable Macro that we can have in our Excel VBA toolbox. Sounds like a good idea doesn’t it? I want to save this in our Personal Macro Workbook. This way it will be available to use in an Excel workbook I want. If you want to read a bit more about the Personal Macro Workbook then I suggest you check out my blog posts below which you should find useful.

Step 1. Open Visual Basic by hitting ALT+F11, or select the Developer Tab and in the Code Group Select Visual Basic. Once you have opened the Visual Basic Editor, we need to add a New Module into our Personal Macro Workbook to store our code.

Step 2. We declare one variable.

DIM ws As Worksheet. This simply instructs Excel to create a memory container for the worksheets.

Step 3. We use the For Each Loop, to begin to loop through each of the worksheets in our active workbook and set the visible setting to TRUE.

Step 4. When all of the worksheets have been set to visible, the routine ends.

Step 5. Test Your Macro!

Want The Code?

Sub UnhideMyWorksheets()

‘Declare variable
Dim ws As Worksheet

‘Use the For Each loop to loop through all of the worksheets in our active workbook
For Each ws In ActiveWorkbook.Worksheets

‘Change the visible setting on all worksheets to visible
ws.Visible = True

‘Move onto the next worksheet
Next ws

End Sub

What Next? Want More Excel Tips?

If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Formula Friday series. Click on the link below

How To Excel At Excel – Formula Friday Blog Posts.

How To Excel At Excel

Unhide Multiple Worksheets At Once With A Small Piece Of VBA Code – Macro Mondays

Let’s turn This Code Into A Reusable Excel Macro.

Want The Code?

What Next? Want More Excel Tips?

If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Formula Friday series. Click on the link below

Recent Posts