When hiding worksheets, for example, there must be at least one visible sheet. This is the basic code, so you may need to add additional error checks.
'Loop through each selected worksheet For Each ws In ActiveWindow.SelectedSheets 'Create variable to hold worksheets Dim ws As Worksheet
The following code loops through all the selected worksheets: Sub LoopThroughSelectedSheets() The ActiveWindow has an object called SelectedSheets, which contains an array of… (yes, you’ve guessed it!) the sheets selected. Normally with VBA, we would loop through the worksheets of the ActiveWorkbook, but in this scenario, we want to use the ActiveWindow object instead.
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.ĭownload the file: 0030 Loop through selected sheets with VBA.zip
I recommend you download the example file for this post. Secondly, we’ll look at using this technique to automate actions that are usually applied one sheet at a time. First, we’ll look at how we can loop through the selected sheets. In the code examples below, we will solve this problem. It can’t be done in Excel therefore, it’s no surprise it can’t be done in VBA either. For example, if you select two worksheets and try to apply worksheet protection, you will notice the option is greyed out. It isn’t a VBA issue it’s an Excel issue. This occurs where the action can only be performed one sheet at a time. Depending on the action being performed, this can cause an error. However, we don’t always want to apply a macro to every worksheet sometimes, we only want it for each selected sheet. This helps you to accessing workbook or sheet without selecting or activating it.Looping through worksheets is one of the most common actions performed by those who use VBA to automate Excel. It is good practice to set the required workbook or sheet to an object. We can also select required workbook when we have more than one Excel Workbook is opened. When we have multiple worksheets in a workbook, we can select required sheet using VBA. VBA to Select Workbook or Worksheet in Excel
For example workbook name is “Book2” which we want to activate, it contains sheet named as “Sheet1”. If you know the name of the workbook, you can use the following VBA code to Activate Workbook or worksheet.