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.

Sub SortIt2() 'VBA macro to Loop through sheets and sorts Cols A:E in Ascending order.
Dim ws As Worksheet

For Each ws In Sheets 'Start of the VBA looping procedure.
ws.Range("A2", ws.Range("E1048576").End(xlUp)).Sort ws.[A2], 1 'Insert No 2 at end for Descending order
Next ws
End Sub


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.


Sub CopyIt()
Sheets.FillAcrossSheets Sheet1.[a1].CurrentRegion
End Sub

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.


Sub MovethroughWB() 'Excel VBA to exclude sheet3 from the loop.
Dim ws As Worksheet

For Each ws In Sheets 'This statement starts the loop
If ws.Name <> "Sheet3" Then 'Perform the Excel action you wish (turn cell yellow below)
ws.Range("A10:A20").Interior.Color=vbYellow
End If
Next ws
End Sub

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.


Sub MovethroughWB1() 'Excel VBA looping procedure, loop excludes first tab on the left.
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets 'Start the VBA Loop.
If ws.Index <> 1 Then 'Exclude the first sheet on the left from the procedure.n 'Perform the Action you wish.
ws.Range("B10:B20").Interior.Color=vbCyan
End If
Next ws
End Sub

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.


Sub LoopMissing2sheets() 'Excel VBA loop to exclude first and last tab (in order)
Dim i As Integer

For i= 2 To Worksheets.Count - 1 'Start of the VBA loop
Sheets(i).[b10].Interior.Color=vbBlue
Next i
End Sub


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.

Sub LoopCertain() 'Excel VBA to loop through only include sheets (1-3)
Dim sh As Worksheet

For Each sh In Sheets ' Start of the VBA loop
Select Case sh.Name
Case Is ="Sheet1", "Sheet2", "Sheet3"
sh.[b11].Interior.Color=vbRed
End Select
Next sh
End Sub

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.

Sub LoopCertain() 'Excel VBA to exclude sheets(1-3)
Dim sh As Worksheet

For Each sh In Sheets
Select Case sh.Name
Case Is ="Sheet1", "Sheet2", "Sheet3"
'No Code here if excluded
Case Else
sh.[b11].Interior.Color=vbRed
End Select
Next sh
End Sub

Once again the next VBA code snippet is another method of looping through the sheets of your choice.

Sub LoopCertain2() 'Excel VBA to loop and only include sheets(1-3)
Dim sh As Variant
For Each sh In Array("Sheet1", "Sheet2", "Sheet3") 'Items in the array are included in the VBA loop.
Sheets(sh).[b10].Interior.Color=vbYellow
Next sh
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.

Sub CopyIt2() 'Excel VBA to fill the current region of Sheet1 to sheets 3 & 5
Dim ar As Variant

ar=Array("Sheet1", "Sheet3", "Sheet5") 'Set up the array
Sheets(ar).FillAcrossSheets Sheet1.[a1].CurrentRegion
End Sub


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.