Looping Through Worksheets / Sheets with VBA
Looping through worksheets with Excel VBA is generally done when you want to perform the same task repeatedly through the workbook. There are a number of ways to loop through the sheets in an Excel workbook. The looping method you choose in up to you. The following is an example of looping through each sheet and sorting the data in ascending order with VBA.
For Each ws In Sheets 'Start of the VBA looping procedure.
Here is a cool way to copy the contents of one Excel sheet to all of the other sheets in the same workbook. This VBA procedure is done without the use of a loop. Basically copying the data from one sheet to all sheets.
The following YouTube video outlines some of the methods on this page, demonstrating how to loop through sheets with Excel VBA.
Looping Through Worksheets Excluding Some Sheets
Occasionally you may want Excel to exclude certain sheets from your VBA loop. Mostly you will want to loop through like sheets and exclude sheets which perform other functions. As with everything in Excel there are many ways to perform this task inside the looping construct. I will demonstrate some of the more popular methods of looping through a worksheet with VBA.
The following will exclude 1 sheet from the VBA looping process.
Dim ws As Worksheet
For Each ws In Sheets 'This statement starts the loop
The above Excel macro will loop through all of the sheets in an Excel workbook except the sheet with the name "Sheet3". This is handy if there are not too many sheets you wish to exclude in the Excel workbook. The VBA loop cycles through each worksheet and colours the range from A10 to A20 yellow.
Quite often you may want to exclude certain sheets from the looping construct which are in a certain position in the workbook. For example, if you wanted to exclude the first tab in the Excel workbook, the tab on the far left, then here is a method for doing just that.
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets 'Start the VBA Loop.
Taking this a step further, the following Excel VBA procedure will include all of the sheets between the first and last sheet in the workbook. So the first sheet on the far left of the workbook (first sheet) and the sheet on the far right of the workbook (last sheet) will be excluded.
Dim i As Integer
If you want to exclude say the first 3 sheets on the left of the workbook change the= 2 to=4. This will mean the first 3 worksheets starting from the left of the file will be excluded. The same is true at the end of the workbook. If you wanted Excel VBA loop to exclude the last two sheets on the far right of the workbook just change - 1 to - 2.
You may have a larger workbook and only want the loop to go through a limited number of sheets and perform an action. In this case the following method uses the Select Case method, where the included sheet names are Sheet1, Sheet2 and Sheet3.
Dim sh As Worksheet
You could also add the sheets you want to exclude in a Case Statement. The following will exclude Sheets 1, 2 and 3. It looks similar but sheets 1 - 3 are excluded. Notice there is no VBA code when the condition is true.
Dim sh As Worksheet
'No Code here if excluded
Case Else
sh.[b11].Interior.Color=vbRed
Once again the next VBA code snippet is another method of looping through the sheets of your choice.
Dim sh As Variant
End Sub
If you were trying to pinpoint sheets so the data from a sheet stayed in line with other specific sheets then the same method can be extended to do this. Sheets 1, 3 and 5 would all remain identical.
ar=Array("Sheet1", "Sheet3", "Sheet5") 'Set up the array
Sheets(ar).FillAcrossSheets Sheet1.[a1].CurrentRegion
After the above VBA looping code has run, Sheets 1, 3 and 5 will all contain the same data as exists in Sheet1 A1 current region. The current region is all of the cells that are continuously used from A1. It is the same as putting your cursor on A1 and pressing Ctrl Shift 8 at the same time. If you have your data setup in a tabular format then all of the data will be identical on sheets (1,3,5). A point of caution with the above example, the sheet you are copying the data from ("Sheet1") needs to be in the Array.
So there are a range of Excel VBA methods at your disposal to do a number of different looping tasks within a workbook. The above examples open the smallest window into the world of looping in Excel VBA. There is far more out there and this article gives the smallest glimpse into the world of looping with VBA. Go out and try new things and always try and improve your VBA code.