Looping through worksheets is one of the most common actions performed by those who use VBA to automate Excel. However, we don’t always want to apply a macro to every worksheet; sometimes, we only want it for each selected sheet. Depending on the action being performed, this can cause an error.
This occurs where the action can only be performed one sheet at a time. It isn’t a VBA issue; it’s an Excel issue. For example, if you select two worksheets and try to apply worksheet protection, you will notice the option is greyed out.
It can’t be done in Excel; therefore, it’s no surprise it can’t be done in VBA either.
In the code examples below, we will solve this problem. First, we’ll look at how we can loop through the selected sheets. Secondly, we’ll look at using this technique to automate actions that are usually applied one sheet at a time.
Download the example file: Join the free Insiders Program and gain access to the example file used for this post.
File name: 0030 Loop through selected sheets with VBA.zip
Loop through selected worksheets
Normally with VBA, we would loop through the worksheets of the ActiveWorkbook, but in this scenario, we want to use the ActiveWindow object instead.
The ActiveWindow has an object called SelectedSheets, which contains an array of… (yes, you’ve guessed it!) the sheets selected. Perfect for our needs.
The following code loops through all the selected worksheets:
Sub LoopThroughSelectedSheets() 'Create variable to hold worksheets Dim ws As Worksheet 'Loop through each selected worksheet For Each ws In ActiveWindow.SelectedSheets 'Perform action. E.g. hide selected worksheets ws.Visible = xlSheetVeryHidden Next ws End Sub
This is the basic code, so you may need to add additional error checks. When hiding worksheets, for example, there must be at least one visible sheet. So, we will either need to count the visible worksheets before commencing or ignore errors. The error checks to be applied will be dependent on your specific requirements.
The macro above works where the action can be performed while other sheets are selected.
Applying single worksheet actions with a loop
Since some actions can only be applied one sheet at a time. For these, we need to change our approach. If we were to do this manually, we would:
- Remember which worksheets we had selected
- Apply the action to each sheet individually
- Re-select the same worksheets
Well, that’s exactly what we are going to do, but with VBA.
The comments in the code will help you understand what each line of macro does.
Sub SingleActionWhenLoopingThroughSelectedSheets() Dim ws As Worksheet Dim sheetArray As Variant Dim myPassword As Variant 'Capture the selected sheets Set sheetArray = ActiveWindow.SelectedSheets 'Loop through each selected worksheet For Each ws In sheetArray 'Select the worksheet ws.Select 'Perform action. e.g. Protect each sheet ws.Protect Next ws 'Reselect the sheets sheetArray.Select End Sub
Remember, depending on the action being performed, you may need to add additional error checks.
How to stop the screen flashing?
With VBA, we should perform most actions without selecting or activating anything; it makes the macro more efficient. However, with this solution, that’s not an option. As a result, the screen can flashes while the macro runs. To avoid this, we can change the Application.ScreenUpdating property at the start and end of the macro
Insert the following directly after the Sub has been declared, and before declaring any variables:
Application.ScreenUpdating = False
Insert the following directly before the End Sub statement:
Application.ScreenUpdating = True
Working with chart sheets
In Excel’s mind, chart sheets are not the same as worksheets. So, if you have any chart sheets included in your selection, you may need to make a small alteration to include all sheet types.
Dim ws As Worksheet
Dim ws As Object
So, there you have it. That’s how we loop through selected sheets. It has a few more potential issues than looping through all sheets But, by thinking like a human (you are one after all), we can create a suitable solution.
Discover how you can automate your work with our Excel courses and tools.
The complete program for saving time by automating Excel.
Excel Automation Secrets
Discover the 7-step framework for automating Excel.
Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.