Hide Specific Excel Sheets With Macro
In a workbook, you might have some sheets that everyone uses, and other sheets that only one or two people need to use, for Admin functions.
For example, the workbook shown below has a data entry sheet for orders, and two Admin sheets — one for lists and one for workbook options.
To make it obvious which sheets are for Admin functions, I used an “Admin_” prefix for those sheets.
Hide the Admin Sheets
To prevent accidental changes to the Admin sheets, and to keep the workbook simpler to use, the Admin sheets can be hidden.
You can right-click on each Admin sheet tab, and click Hide, to manually hide each sheet. Or, to make the job easier, you can use a macro to hide the sheets, and another macro to show them.
To run a macro:
- Click the View tab on the Ribbon, then click Macros.
- Click on a macro name to select it, and click Run
Create a Macro Shortcut
To make it even easier to run the Admin sheet macros, you can create a keyboard shortcut for each macro.
- Click the Developer tab on the Ribbon, then click Macros.
- Click on a macro name to select it, and click Options
- In the Shortcut Keys section, type a letter (upper or lower case) in the box. In the screen shot below, the Shift key was pressed, while typing the letter H.
Using the Macro Shortcuts
To run the HideAdminSheets macro now, you can press Ctrl + Shift + H.
There’s also a shortcut for the ShowAdminSheets macro — Ctrl + Shift + A.
I don’t use too many macro shortcuts, because it’s hard to remember more than a few. They’re handy though, when you’re editing a workbook, and want to quickly show all the sheets, or hide the Admin sheets.
Watch the Excel Macro Shortcuts Video
To see the steps for creating and using the macro shortcuts, you can watch this short video tutorial.