You can use the following methods to loop through sheets in an Excel workbook using VBA:
Method 1: Loop Through All Worksheets
Sub LoopSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Range("A1").Value = 100
Next ws
End Sub
This particular macro loops through each sheet in a workbook and sets the value in cell A1 of each sheet to be equal to 100.
Method 2: Loop Through All Worksheets, Excluding Specific Ones
Sub LoopSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case Is = "Sheet2", "Sheet3"
'Do not execute any code for these sheets
Case Else
ws.Range("A1").Value = 100
End Select
Next ws
End Sub
This particular macro loops through each sheet in a workbook and sets the value in cell A1 of each sheet to be equal to 100, except for the sheets called Sheet2 and Sheet3.
The following examples show how to use each of these methods in practice with an Excel workbook that contains four empty sheets:
Example 1: Loop Through All Worksheets
We can use the following macro to set the value in cell A1 of each sheet in our workbook to be equal to 100:
Sub LoopSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Range("A1").Value = 100
Next ws
When we run this macro, the value of cell A1 in each sheet in the workbook will be equal to 100:
Example 2: Loop Through All Worksheets, Excluding Specific Ones
Suppose we would like to loop through each worksheet and set the value of cell A1 in each sheet to be equal to 100, except for the sheets called Sheet2 and Sheet3.
We can create the following macro to do so:
Sub LoopSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case Is = "Sheet2", "Sheet3"
'Do not execute any code for these sheets
Case Else
ws.Range("A1").Value = 100
End Select
Next ws
End Sub
When we run this macro, we will see that both Sheet1 and Sheet4 have a value of 100 in cell A1.
However, Sheet2 and Sheet3 will not have any value in cell A1 since we used the Case function to skip over these sheets:
Note that in these examples we looped through each worksheet and set the value of one cell equal to a specific value just for simplicity’s sake.
However, you can use similar syntax with a For Each statement to perform much more complicated tasks in each sheet if you’d like.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Count Number of Rows in Range
VBA: How to Count Cells with Specific Text
VBA: How to Write COUNTIF and COUNTIFS Functions
