Excel VBA Loop Through Specific Worksheets

VBA & macros · Updated November 22, 2026

To loop through specific worksheets in Excel VBA, use a For Each loop with a If condition that checks each worksheet’s name or property, such as If ws.Name Like "*Sales*" Then. This lets you target only the sheets you want while skipping others, and you can also loop through all sheets and filter by index, tab color, or visibility.

Why You Need a Filtered Loop

A plain For Each ws In ThisWorkbook.Worksheets loop touches every sheet in the workbook, including hidden ones and charts. That is often fine for small files, but in real workbooks you usually have a mix of data sheets, summary sheets, and reference sheets. Running a macro that formats every sheet can overwrite formatting on sheets you never meant to touch, or slow down the macro on large workbooks. Filtering the loop to specific sheets keeps the action predictable and reduces the chance of accidental edits.

Common reasons to filter include: updating only monthly report tabs, formatting only sheets whose names contain a region, or processing only visible sheets. The filter can be based on the sheet name, its index number, a cell value, or even a custom property you set earlier.

How the For Each Loop Works

The For Each loop in VBA iterates over a collection. In Excel, ThisWorkbook.Worksheets is a collection of all worksheet objects. The loop variable (usually named ws) holds a reference to each worksheet one at a time. Inside the loop, you write code that acts on that sheet. To skip sheets, you add an If statement before the main code.

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If ws.Name Like "*Data*" Then
        ' your code here
        ws.Range("A1").Value = "Processed"
    End If
Next ws

The Like operator uses wildcards: * matches any number of characters, ? matches a single character. So "*Data*" matches any sheet name that contains the word “Data” anywhere. If you need an exact match, use If ws.Name = "Sales" Then.

Looping by Sheet Index or Tab Color

Sometimes names are not reliable, especially if users rename tabs. You can filter by the sheet’s position in the workbook using its Index property. The index is the tab order from left to right, starting at 1.

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If ws.Index >= 3 And ws.Index <= 7 Then
        ' process sheets 3 through 7
    End If
Next ws

Tab color is another filter. You can check the Tab.Color property, which returns an RGB value. For example, to process all sheets with a red tab:

If ws.Tab.Color = RGB(255, 0, 0) Then

This is useful when you color-code tabs by category. Note that Tab.Color returns xlColorIndexAutomatic (-4105) for default tabs, so check for that if you want to skip unstyled tabs.

Using Select Case for Multiple Conditions

When you have several specific sheets to target, a Select Case statement is cleaner than multiple If statements. You can list exact names or use patterns.

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    Select Case ws.Name
        Case "Summary", "Q1", "Q2", "Q3", "Q4"
            ' process these five sheets
        Case Else
            ' do nothing
    End Select
Next ws

You can also combine Select Case with Like for pattern matching, but note that Select Case does not support Like directly in the Case line. Instead, use Case Is with a boolean expression or fall back to If statements.

Common Mistakes and How to Avoid Them

One frequent error is modifying the worksheet collection while looping. For example, if you delete a sheet inside a For Each loop, VBA may throw an error or skip sheets. To delete sheets safely, collect the ones to delete in a separate array or use a reverse loop with For i = Worksheets.Count To 1 Step -1.

Another mistake is forgetting to qualify the sheet reference. Inside the loop, always use ws.Range(...) rather than just Range(...). Unqualified Range refers to the active sheet, which may not be the one you are looping through. This causes the macro to write to the wrong sheet.

Also, be careful with hidden sheets. By default, For Each includes hidden sheets. If you only want visible sheets, add a check: If ws.Visible = xlSheetVisible Then. This is a common need because hidden reference sheets often contain formulas you do not want to alter.

Performance and Speed Considerations

If your loop processes many sheets and does a lot of cell operations, it can be slow. Turn off screen updating and calculation at the start, then turn them back on at the end.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' ... loop code ...
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Remember to reset these even if an error occurs. Use error handling with On Error GoTo to ensure the settings are restored. For very large loops, consider reading data into a VBA array, processing it in memory, and writing it back in one operation.

Alternatives to Looping

Sometimes you do not need a loop at all. If you want to apply the same formatting to multiple sheets, you can group them first by holding Ctrl and clicking their tabs, then apply the formatting once. For data manipulation, Excel's built-in features like Power Query or the FILTER function (in Excel 365) can handle many tasks without VBA.

If you need to run a macro on a list of sheet names stored in a range, you can loop through that range instead of the workbook's sheets. This gives you a dynamic list that users can edit without touching the code.

Dim cell As Range
For Each cell In Range("A1:A5")
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(cell.Value)
    ' process ws
Next cell

Frequently Asked Questions

How do I loop through only visible sheets?
Add a condition inside the loop: If ws.Visible = xlSheetVisible Then. This skips hidden and very hidden sheets. You can also check ws.Visible = xlSheetHidden if you specifically want hidden ones.

Can I loop through sheets in a different workbook?
Yes. Use Workbooks("MyWorkbook.xlsx").Worksheets instead of ThisWorkbook.Worksheets. Make sure the workbook is open. You can also use Workbooks.Open to open it first.

What if the sheet name contains a space or special character?
Use the exact name in quotes, like If ws.Name = "Sales Report 2024" Then. For pattern matching with spaces, the Like operator works fine, e.g., If ws.Name Like "*Report*" Then.

How do I stop the loop early?
Use Exit For inside the loop when a condition is met. For example, if you find the sheet you need and want to stop processing further sheets.

Why does my loop skip every other sheet?
This usually happens if you delete a sheet inside a For Each loop. The collection shifts and the loop skips the next sheet. Use a reverse numeric loop or collect sheets in an array before deleting.

Can I loop through sheets based on a cell value?
Yes. Read the value into a variable before the loop, then compare each sheet's name or a cell on that sheet. For example, check if ws.Range("A1").Value = "Yes" to process only sheets marked for processing.

Before running any macro, save a copy of your workbook. Macros cannot be undone with Ctrl+Z, and a small mistake in a loop can modify many sheets at once. Test on a backup first.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.