Hello Excellers. Welcome to #macromonday in my #Excel series. Are you working on a project with multiple worksheets and want to keep the audience focused on the one-sheet you’re working with? Then, you can hide all other worksheets except for the currently active one. With just a few simple steps, you can create a macro that will do this for you. Please keep reading for instructions on how to set it up.
So today, let’s look at a simple VBA code that loops through all of the worksheet names in your Excel workbook. Excel hides the worksheets if they do not match the active worksheet’s name. However, excel will not let you hide all worksheets in a workbook; you have to have at least one visible.
So, depending on the type of spreadsheet I am developing, I often have multiple worksheets in an Excel workbook. These typically comprise a Data Sheet, a Calculations Sheet and a Results Sheet. I find this Macro a speedy way to hide all of the extra worksheets without having to run through them all and hide them individually.
What Does The Macro Do?
This Macro hides all of the worksheets in your Excel workbook except the active worksheet which remains visible.
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.
Before we 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’s the difference?.
If you save the Macro in your Personal Macro workbook, it will be available in your 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 VBA code repeatedly so that in this instance, I store it in my Personal Macro Workbook. When you select this option, Excel creates (if it does not already exist) and saves the Macro in that location. By default, this macro workbook is named Personal.xlsb. I intend (and do) use this Macro repeatedly, so I store it in my Personal Macro Workbook.
Step 1. Insert A New Module. Name The Macro. Name Any Variables.
Insert a new module into your Personal Macro workbook. Select your workbook from the Project Explorer pane and hit Insert Module. The next step is to name your Macro. In this example I have named it DeleteNonActiveWorksheets. Note that Excel automatically inserts the End Sub line of code. I declare an object called was. This will create a memory container for each Excel worksheet that the Macro loops through.
Step 2. Use the For Each Loop.
Excel begins looping through the worksheets in the workbook. (Note there is a difference between ThisWorkbook and ActiveWorkbook. ThisWorkbook object is the workbook that the code is contained in and the ActiveWorkbook object is the workbook that is currently active and being used. We use ThisWorkbook to ensure that we do not affect any other workbooks, as the workbook that contains the code maybe not always be the active workbook.
Step 3. Compare The Active Sheet Name
The macro at this stage compares the name of the active sheet to the worksheet that is being looped through. Each of the Excel worksheet names are compared to the active worksheet name.
Step 4. Do The Names Match The Active Worksheet Name?
If the names do not match or are not equal “<>”. then Excel hides the worksheet.
Step 5. Looping Through All Workskeets
The last stage is to loop back to the next sheet, where the procedure is repeated, until all of the Excel worksheets are evaluated and the macro ends.
Here is the code if you want to copy and use for free.
Sub DeleteNonActiveWorksheets()
'macro by how to Excel at Excel
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> ThisWorkbook.ActiveSheet.Name Then
ws.Visible = xlSheetHide
End If
Next ws
End Sub
