Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Loop through selected sheets with VBA

VBA Code Snippets

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.

Error message when applying protection

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.

Option greyed one when apply to multiple sheets

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

I recommend you download the example file for this post.  Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.

Download Icon
Download the file: 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:

  1. Remember which worksheets we had selected
  2. Apply the action to each sheet individually
  3. 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.

Change this:

Dim ws As Worksheet

To this:

Dim ws As Object

Conclusion

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.

Happy looping!

Related posts:


Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).


Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

2 thoughts on “Loop through selected sheets with VBA

  1. kati livingood says:

    I am trying to use this code to look some changes to a selection of workseets in a workbook.. it looks like it will work however it is not clear how to name the worksheets to be included in the array. The example file is only one worksheet so it did not show me this part of the code either.

    • Excel Off The Grid says:

      The code is working on the “selected” sheets. You’ll need to modify the approach if you want to name specific worksheets.

Leave a Reply

Your email address will not be published. Required fields are marked *