Hello Excellers and welcome back to another #MacroMonday #Excel blog post in my Excel 2020 series. Today I will show you how to write some Excel VBA code that will hide all of your Excel worksheets except the active one in your Excel workbook. This will save you a lot of time hiding individual worksheets one at a time. Let’s get coding!.
Starting The VBA 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.
Do You Want To Watch The Video?
Before you begin to write any code you need to decide where to store the code. You have a choice.
- To store your code either in your Personal Macro Workbook or
- Store it in your current workbook
What Is The Difference?
Well, if you save the code 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. In this instance, I may want to reuse the code so I will store it in my Personal Macro Workbook. When you select this option then Excel creates (if it is not already created) this workbook and saves the macro in that location. By default, this macro workbook is named Personal.xlsb. This is a really useful macro that I want to reuse over and over again. So, I will make sure to save this in my Personal Macro Workbook.
If you want more details on creating and updating your personal macro workbook then I recommend my blog posts below.
Creating and Updating Your Personal Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Create A Shortcut To Your Personal Excel Macro Workbook
Write The Code.
I have inserted a New Module into my Personal Macro Workbook. Type Sub then the name of your Excel macro. In this example I have named my Macro ActiveWorksheetOnly.
As you type the name of the Macro and hit return, Excel will automatically insert End Sub. All that is needed is the rest of the code in between these two lines of code.
[stextbox id=’info’]
Sub ActiveWorksheetOnly()
End Sub
[/stextbox]
Declaring Variables.
Next, I need to declare any variables I need for the Macro. Declaring a variable or variables simply allocates Excel memory for it. Essentially this is memory container for this value in Excel. I have only one variable to declare.
[stextbox id=’info’]
Dim ws as Worksheet
[/stextbox]
Loop Through Worksheets In The Workbook.
The next line of code loops through all of the worksheet on the current workbook. It uses the 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.
[stextbox id=’info’]
For Each ws In ThisWorkbook.Worksheets
[/stextbox]
Notice that I use ThisWorkbook. The ThisWorkbook object refers to the workbook that the code is contained in. The ActiveWorkbook object refers to the workbook that is currently active. They often return the same object, but if the workbook running the code is not the active workbook, they return different objects. In this case, I don’t want to risk hiding sheets in other workbooks, so I use ThisWorkbook.
Check the Worksheet Name.
The next bit of code compares the active worksheet name to the active worksheet name.
[stextbox id=’info’]
If ws.Name <> ThisWorkbook.ActiveSheet.Name Then
[/stextbox]
Hiding The Worksheets That Are Not Active.
If the worksheet name is not equal to the active worksheet name decided by the line of code above, then the worksheet is hidden.
[stextbox id=’info’]
ws.Visible = xlSheetHidden
[/stextbox]
To hide the worksheet Excel sets the worksheet visible property to hidden. Alternatively, you could set the visible property to xlSheetVeryHidden. This means that when the user right-clicks to unhide a worksheet it is not available.
The user would need to go into the Visual Basic Editor to make the worksheets visible again.
Looping To The Next Worksheet.
The next bit of code loops back to the next worksheet, until all of the worksheet shave been compared to the name of active worksheet.
[stextbox id=’info’]
Next ws
[/stextbox]
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.
[stextbox id=’info’]
End Sub
[/stextbox]
Copy The Full Excel Macro.
[stextbox id=’info’]
Sub ActiveWorksheetOnly()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> ThisWorkbook.ActiveSheet.Name Then
ws.Visible = xlSheetHidden
End If
Next ws
End Sub
[/stextbox]
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