How to Loop Through All the Sheets using VBA in Excel

Home ➜ VBA ➜ How to Loop Through All the Sheets using VBA in Excel

There two ways to loop through all the sheets that you have in a workbook.

• FOR NEXT LOOP     
• FOR EACH LOOP

1. Using For Each Loop

As you know with FOR EACH you can loop through all the objects in the collection and in a workbook worksheets are a collection of all the worksheets.

Use the following steps:

1. First, declare a variable to refer to a worksheet for the loop.
   
2. After that, start the loop with the keyword “For Each” and refer to each worksheet in the workbook.
   
3. Now let’s say you want to enter a value in the cell A1 of each worksheet you can use write code like following.
   
4. In the end, use the keyword “End” to end the loop.
   

Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook

Full Code

Sub vba_loop_sheets()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

    ws.Range("A1").Value = "Yes"

Next ws

End Sub

This code loops through each sheet and enters the value in the cell A1 of each sheet. The benefit of using this method is it loops through all the sheets in the workbook.

And if you want to loop through all the worksheets into a close workbook, use code like below.

Sub vba_loop_sheets()

Dim wb As Workbook
Dim ws As Worksheet

Application.DisplayAlerts = False

Set wb = Workbooks.Open("C:\Users\Dell\Desktop\sample-file.xlsx")

For Each ws In wb.Worksheets
    ws.Range("A1").Value = "Done"
Next ws

wb.Close SaveChanges:=True

Application.DisplayAlerts = True

End Sub

2. Use the For Next Loop

You can also loop by using the FOR NEXT loop, where you can use the count of the sheets to decide the number of loops to perform and use the loop counter to refer to each sheet.

Here are the steps you need to follow:

1. First, you need to declare two variables to store count value for the loop and one for the count of the sheets that you have in the workbook.
   
2. Now, set the value for the “shtCount” variable equivalent to the number of sheets that you have in the workbook.
   
3. After that, start the code for loop using the “For i” keyword and use the sheet count of the max value for the loop counter.
   
4. From here, you need to use the loop counter to loop through all the sheets and enter value “Yes” in the cell A1 of every sheet.
   

Full Code

Sub vba_loop_sheets()

Dim i As Long
Dim shtCount As Long

shtCount = Sheets.Count

For i = 1 To shtCount

    Sheets(i).Range("A1").Value = "Yes"

Next i

End Sub

And if you want to loop through a workbook that is closed then use the following code.

Sub vba_loop_sheets()

Dim i As Long
Dim shtCount As Long

Set wb = Workbooks.Open("C:\Users\Dell\Desktop\sample-file.xlsx")
shtCount = wb.Sheets.Count

Application.DisplayAlerts = False

For i = 1 To shtCount

    wb.Sheets(i).Range("A1").Value = "Yes"

Next i

wb.Close SaveChanges:=True

Application.DisplayAlerts = True

End Sub