Hide Specific Excel Sheets With Macro

hiddenIn 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.

AdminSheetsHide01

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.

AdminSheetsHide02

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

AdminSheetsHide03

  • 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.

AdminSheetsHide04

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.

_________

You may also like...

4 Responses

  1. AlexJ says:

    Debra,
    Two things I like:
    1. Use the worksheet codename rather than the sheet name
    2. How about: If ws.Name Like “Admin_*”

  2. Thanks AlexJ — good suggestions!

  3. Tony says:

    I’m trying to find some code that will help me hide certain worksheets, based on information run in the previous macro. I have 5 worksheets in total that depending on the information must stay open while the other are hidden. This may change to different worksheets when I run the 1st macro again.

    Many thanks
    Tony

  4. PS says:

    l have 10 worksheets in total. l run a macro to hide all except “main”. On the “main” sheet, l would like to run another macro to show 3 of the hidden sheets. Please help… Thank you

Leave a Reply to PS Cancel reply

Your email address will not be published. Required fields are marked *