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