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”