Home ➜ VBA Tutorial ➜ 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:
- First, declare a variable to refer to a worksheet for the loop.
- After that, start the loop with the keyword “For Each” and refer to each worksheet in the workbook.
- Now let’s say you want to enter a value in the cell A1 of each worksheet you can use write code like following.
- 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:
- 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.
- Now, set the value for the “shtCount” variable equivalent to the number of sheets that you have in the workbook.
- 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.
- 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
More Tutorials on VBA Worksheets
- How to Insert a New Sheet using VBA
- How to Activate a Sheet using VBA
- How to Copy a Sheet using VBA
- How to Rename a Sheet using VBA
- How to Hide a Sheet using VBA
- How to Delete a Sheet using VBA
- How to Count Sheets using VBA
- How to Protect a Sheet using VBA
- How to Clear an Entire Sheet using VBA
- How to Check is a Sheet Exists using VBA
- Back to VBA Worksheet / VBA Tutorial