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.
- 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
Let’s turn This Code Into A Reusable Excel 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