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.
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
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.
Sub HideSheets()
End Sub
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.
Dim ws as Worksheet
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.
Application.DisplayAlerts = False
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.
For Each ws In Worksheets
If ws.Name <> ActiveSheet.Name Then
ws.Visible = xlSheetHidden
End If
Next ws
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.
Application.DisplayAlerts = True
Ending The Macro. How to hide Excel Worksheets
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.
End Sub
Now that you know how to hide all your Excel worksheets except the active one, you can keep your work area neat and tidy. This simple macro will save you time and frustration when working on multiple projects at once. Leave us a comment letting us know what other helpful tips and tricks you would like to see covered here on the blog.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips every month and receive my free Ebook, 50 Excel Tips.
Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below
How To Excel At Excel – Macro Mondays Blog Posts.
