Macro 19: Unhide All Worksheets in a Workbook
If you’ve ever had to unhide multiple sheets in Excel, you know what a pain it is. You need to click
four times for each sheet you want to unhide. Although that may not sound like a lot, try to unhide
ten or more sheets — it gets to be a pain fast. This macro makes easy work of that task.
How it works
This macro loops the worksheets and changes the visible state.
Sub Macro19()
‘Step 1: Declare your variables
Dim ws As Worksheet
‘Step 2: Start looping through all worksheets
For Each ws In ActiveWorkbook.Worksheets
‘Step 3: Loop to next worksheet
ws.Visible = xlSheetVisible
Next ws
End Sub
1. Step 1 declares an object called ws. This creates a memory container for each worksheet the
macro loops through.
2. In Step 2, the macro starts the looping, telling Excel to enumerate through all worksheets in
this workbook.
3. Step 3 changes the visible state to xlSheetVisible. Then it loops back to get the next
worksheet.
How to use it
The best place to store this macro is in your Personal Macro Workbook. That way, the macro is always
available to you. The Personal Macro Workbook is loaded whenever you start Excel. In the VBE Project
window, it is named personal.xlsb.
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click personal.xlb in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code in the newly created module.
If you don’t see personal.xlb in your project window, it means it doesn’t exist yet. You’ll have to
record a macro using Personal Macro Workbook as the destination.
To record the macro in your Personal Macro Workbook, select the Personal Macro Workbook option
in the Record Macro dialog box before you start recording. This option is in the Store Macro In
drop-down box. Simply record a couple of cell clicks and then stop recording. You can discard
