How to Loop Through Worksheets in a Workbook in Excel VBA?

Using the For Each Loop

The code below loops through all worksheets in the workbook, and activates each worksheet. The code uses the “for each” loop to loop through the wrosheets contained inside ThisWorkbook. After it is done looping through all the worksheets, it reactivates the original worksheet that was active before running the macro. The comments highlight how the “re-activation” is done.

For the sake of an example on how to access each worksheet, the macro also sets cell A1 of each worksheet to “1”.

Sub loop_through_all_worksheets()

Dim ws As Worksheet
Dim starting_ws As Worksheet
Set starting_ws = ActiveSheet 'remember which worksheet is active in the beginning

For Each ws In ThisWorkbook.Worksheets
    ws.Activate
    'do whatever you need
    ws.Cells(1, 1) = 1 'this sets cell A1 of each sheet to "1"
Next

starting_ws.Activate 'activate the worksheet that was originally active

End Sub

Using the For Loop

An alternative approach, instead of using the “For each” loop, and going through each worksheet in the ThisWorkbooko object, is to use the For loop. Basically each worksheet can be accessed by a number. The first worksheet is 1, second is 2, third is 3, etc. You can use the for loop to access worksheets by calling “ThisWorkbook.Worksheet(i)”, with “i” being the number.

Sub loop_workbooks_for_loop()

Dim i As Integer
Dim ws_num As Integer

Dim starting_ws As Worksheet
Set starting_ws = ActiveSheet 'remember which worksheet is active in the beginning
ws_num = ThisWorkbook.Worksheets.count

For i = 1 To ws_num
    ThisWorkbook.Worksheets(i).Activate
    'do whatever you need
    ThisWorkbook.Worksheets(i).Cells(1, 1) = 1  'this sets cell A1 of each sheet to "1"
Next

starting_ws.Activate 'activate the worksheet that was originally active

End Sub

Why Use the For Loop Method?

For most intents and purposes, both methods can achieve the same result of looping through all worksheets.

However, if you need to manipulate worksheets based on values of other worksheets (before / after, end / start) or position from other worksheets, having a worksheet count variable “i”, will be very handy.

For example, if cell A1 of the prior worksheet is less than 10, set A1 of active worksheet to 20. You can easily access the prior worksheet cell A1 by calling:

ThisWorkbook.Worksheets(i - 1).Cells(1, 1)

Here you can print the prior tab’s cell A1 value in a message box:

If i > 1 Then MsgBox ThisWorkbook.Worksheets(i - 1).Cells(1, 1)

“i” must be larger than 1, because the first worksheet does not have a worksheet before it. If i is 1, and worksheet #0 is called, then there will be an error. “Run-time error ‘9’: Subscript out of range”