Hello Excellers, welcome back to another #macromonday blog post in my 2019 series of Excel tips. I want to share with you an easy Excel macro which hopefully will save you some time when preparing your Excel workbook solutions. It is an easy way to hide all other worksheets in your Excel workbook except the active worksheet. Just select the worksheet you want to keep visible, run the macro and all other worksheets in your Excel workbook will be hidden. Let’s get started on writing the VBA code!.
Preparing To Write The Macro
First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result. You then have a choice, you can either create a module to store your code either /#’];//in your Personal Macro Workbook or in your current workbook. What’s the difference?. Well, if you save the macro in your Personal Macro workbook it will be available in any Excel workbooks. If you store it in the current workbook then use is restricted to that workbook.
Learn More About Your Personal Macro Workbook (PMW)
If you want to read more about your Excel PMW then check out my blog posts below.
I want to store this macro in my Personal Macro workbook, so I can call it at any time on any workbook.
Starting The Macro
We need to start off the macro by inserting a New Module. Do this by selecting the Personal.xlsbworkbook, then Insert Module. Type Sub then the name of your macro. In this example, I have called it Sub HideSheets. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We simply need to enter the rest of the code between these two lines.
Declaring A Variable
We need to declare a variable. This ensures that Excel creates a memory container for this value. In this example, we need to declare the following. This ensures the user selected range is stored in the Excel memory.
Turn Off Screen Updating
By turning off screen updating we avoid the screen flashing or flickering as the Excel executes the VBA code. Any time a change occurs on your worksheet, the screen will update.
Use The For Next Loop With IF Then.
The next stage is to use For…Next looping. Excel loops through all of the worksheets in the currently active workbook. Every worksheet except the currently active worksheet will have its visible property set to hidden.
Turn Off Screen Updating.
The screen updating we turned off at the start of the Macro we can now reverse and turn it back on which is the default.
Ending The Macro.
Finally, once all of the non-active worksheets in the workbook have been hidden, the code finally ends. This instruction was already entered into the module for us when started the type the name of the Macro.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below