When building automation with Excel VBA, one of the most powerful techniques is the ability to loop through all sheets in a workbook.
Whether you want to update headers, clean up data, generate summaries, or validate formulas — automating tasks across every worksheet saves hours of manual work.

In this comprehensive guide, we’ll explore how to loop through all worksheets until the last one, cover multiple VBA looping techniques, discuss real-world applications, and show you how to handle errors safely when some sheets are hidden or protected.

By the end, you’ll master professional-grade VBA patterns that make your code faster, safer, and easier to maintain.

---

✅ Understanding the Concept of Looping Through Sheets

・What does it mean to loop through all sheets?

Looping through all worksheets means performing a series of actions — like data entry, formatting, or validation — on each sheet in the workbook, one by one, from the first sheet to the last.

For example, if your workbook contains “Sales,” “Finance,” and “HR,” your VBA script will process each in order, automatically moving from one to the next.

---

・Why it’s useful

Instead of writing repetitive code like:

Sheets("Sales").Range("A1").Value = "Done"
Sheets("Finance").Range("A1").Value = "Done"
Sheets("HR").Range("A1").Value = "Done"

You can automate it with just a few lines:

Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
ws.Range("A1").Value = "Done"
Next ws

This approach is cleaner, faster, and scalable, no matter how many sheets exist.

---

✅ Basic Syntax: Looping Through All Worksheets

・Using For Each Loop (Recommended)

Sub LoopAllSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
ws.Range("A1").Value = "Processed"
Next ws
End Sub

This is the simplest and most common pattern.
✅ It automatically handles any number of sheets.
✅ It’s safe and readable.
✅ Works even if sheets are added or renamed later.

---

・Using a Counter (For i = 1 To Sheets.Count)

Sub LoopByIndex()
Dim i As Long
For i = 1 To Sheets.Count
Sheets(i).Range("A1").Value = "Checked " & Sheets(i).Name
Next i
End Sub

This method loops numerically from the first to the last sheet by index number.

---

・Difference between the two

Method	Pros	Cons
For Each ws In Sheets	Clean, dynamic, readable	Slightly slower for extremely large workbooks
For i = 1 To Sheets.Count	Index-based control	Can cause errors if a sheet is deleted mid-loop

For most cases, For Each is the preferred choice.

---

✅ Loop Until the Last Sheet Only (Custom Conditions)

Sometimes, you may not want to process all sheets — only up to a specific point or skip certain ones.

・Example: Stop at the last visible sheet

Sub LoopUntilLastVisible()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
If ws.Visible = xlSheetVisible Then
ws.Range("B1").Value = "Visible Sheet Processed"
End If
Next ws
End Sub

This ensures hidden sheets are ignored.

---

・Example: Exclude specific sheets (like “Summary” or “Config”)

Sub LoopExcludeSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
If ws.Name <> "Summary" And ws.Name <> "Config" Then
ws.Range("C1").Value = "Processed"
End If
Next ws
End Sub

By filtering by name, you avoid overwriting key sheets that store system data or results.

---

✅ Looping from First to Last Sheet in Sequence

If you need explicit control over the sheet order:

Sub LoopSequentially()
Dim i As Long
For i = 1 To ThisWorkbook.Sheets.Count
ThisWorkbook.Sheets(i).Activate
ThisWorkbook.Sheets(i).Range("A1").Value = "Sequence: " & i
Next i
End Sub

✅ This version visually activates each sheet — helpful for debugging or demonstration.
❌ Avoid in production automation — it’s slower due to screen refreshes.

---

✅ Advanced Pattern: Skipping Hidden or Protected Sheets Automatically

When automating real-world Excel reports, some sheets may be hidden or protected. To avoid runtime errors:

Sub LoopSkipHiddenProtected()
Dim ws As Worksheet
On Error Resume Next
For Each ws In ThisWorkbook.Sheets
If ws.Visible = xlSheetVisible Then
ws.Unprotect "password"  'Optional
ws.Range("A1").Value = "Updated on " & Date
ws.Protect "password"
End If
Next ws
On Error GoTo 0
End Sub

This ensures smooth processing while maintaining security.

---

✅ Performing Operations on Each Sheet (Practical Examples)

・Example 1: Add timestamp to each sheet

Sub TimestampAllSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
ws.Range("A1").Value = "Last Updated:"
ws.Range("B1").Value = Now
Next ws
End Sub

---

・Example 2: Format headers consistently

Sub FormatHeaders()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
With ws.Range("A1:D1")
.Font.Bold = True
.Interior.Color = RGB(200, 220, 255)
End With
Next ws
End Sub

---

・Example 3: Summarize data from all sheets

Sub CombineData()
Dim ws As Worksheet
Dim summaryRow As Long
summaryRow = 2
For Each ws In ThisWorkbook.Sheets
If ws.Name <> "Summary" Then
Sheets("Summary").Range("A" & summaryRow).Value = ws.Name
Sheets("Summary").Range("B" & summaryRow).Value = ws.Range("A1").Value
summaryRow = summaryRow + 1
End If
Next ws
End Sub

This creates a central summary sheet listing values from each tab.

---

✅ Handling the Last Sheet Safely

If you specifically need to identify or process only the last sheet, you can reference it directly:

Sheets(Sheets.Count).Activate

Or include logic to perform extra actions when the last sheet is reached:

Sub HighlightLastSheet()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
If ws.Index = ThisWorkbook.Sheets.Count Then
ws.Tab.Color = RGB(255, 100, 100)
End If
Next ws
End Sub

This highlights the last sheet with a red tab — a useful visual indicator in reports.

---

✅ Error Handling in Sheet Loops

・Common errors to handle

Error	Description	Cause
9	Subscript out of range	Sheet doesn’t exist
91	Object variable not set	Worksheet not assigned
1004	Cannot access sheet	Hidden/protected during execution

---

・Add structured error control

Sub LoopWithErrorControl()
Dim ws As Worksheet
On Error GoTo ErrorHandler
For Each ws In ThisWorkbook.Sheets
ws.Range("A1").Value = "Running check..."
Next ws
Exit Sub
ErrorHandler:
MsgBox "An error occurred on sheet: " & ws.Name
Resume Next
End Sub

This displays which sheet failed without stopping the entire macro.

---

✅ Performance Optimization Techniques

When looping through many sheets, speed can suffer due to screen refreshes and recalculations.
Optimize your code by controlling Excel’s environment during execution.

・Turn off screen updates

Application.ScreenUpdating = False

・Turn off automatic calculation

Application.Calculation = xlCalculationManual

・Disable alerts

Application.DisplayAlerts = False

Re-enable everything afterward:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True

With these optimizations, large workbooks process up to 10x faster.

---

✅ Looping with Custom Order or Criteria

・Process only sheets with specific names

Sub LoopSalesOnly()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
If InStr(ws.Name, "Sales") > 0 Then
ws.Range("D1").Value = "Sales sheet found"
End If
Next ws
End Sub

---

・Process only worksheets (ignore chart sheets)

Sub LoopWorksheetsOnly()
Dim ws As Object
For Each ws In ThisWorkbook.Sheets
If TypeName(ws) = "Worksheet" Then
ws.Range("A1").Value = "Checked"
End If
Next ws
End Sub

This ensures chart sheets or macro sheets aren’t included in your loop.

---

✅ Real-World Example: Company-Wide Report Generator

Here’s a more complete example combining all techniques.

Sub GenerateReport()
Dim ws As Worksheet
Dim summary As Worksheet
Dim lastRow As Long
Dim counter As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Set summary = Sheets("Summary")
summary.Range("A2:B1000").ClearContents
counter = 2
For Each ws In ThisWorkbook.Sheets
If ws.Name <> "Summary" And ws.Visible = xlSheetVisible Then
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
summary.Range("A" & counter).Value = ws.Name
summary.Range("B" & counter).Value = lastRow - 1  'Record count
counter = counter + 1
End If
Next ws
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "Report generation completed successfully!", vbInformation
End Sub

✅ Processes all visible sheets
✅ Counts data rows
✅ Updates the “Summary” sheet dynamically
✅ Runs fast and safely

---

✅ Integrating with RPA and Automation Tools

When combining VBA with automation platforms like UiPath or Power Automate:

• Avoid relying on visible sheet activation.
• Use fully qualified references instead.
• Ensure your loops include error handling to prevent execution breaks.

RPA-friendly code:

For Each ws In ThisWorkbook.Sheets
ws.Range("A1").Value = "Processed by bot"
Next ws

This ensures compatibility with background processes that don’t display Excel windows.

---

✅ Debugging Tips During Sheet Loops

✔ Use Debug.Print ws.Name to monitor progress in the Immediate Window.
✔ Add temporary color coding to track progress:

ws.Tab.Color = RGB(200, 255, 200)

✔ Use MsgBox for checkpoints in test mode — remove before production.

These visual and logging cues make complex workbook processing easier to troubleshoot.

---

✅ Best Practices Summary

✔ Use For Each ws In Sheets for dynamic looping
✔ Always skip or unhide hidden sheets when needed
✔ Disable ScreenUpdating for speed
✔ Include error handling with On Error Resume Next or GoTo
✔ Avoid sheet activation unless absolutely required
✔ Protect sensitive sheets again after updates

These principles ensure both stability and performance, even in large enterprise workbooks.

---

✅ Summary：Loop Through All Sheets Like a Pro

• Looping through every sheet allows full workbook automation — no repetitive code.
• Use For Each ws In Sheets for flexibility and safety.
• Apply filters to skip hidden, protected, or special-purpose sheets.
• Combine with performance tuning (ScreenUpdating, Calculation) for speed.
• Integrate structured error handling for robust automation.

By mastering this “loop through all sheets” technique, you can handle any Excel task — from generating multi-department summaries to cleaning data across dozens of tabs — automatically, reliably, and professionally.