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
- Excel Truck Group Jobs: Pay, Hiring & Reviews
- Excel Track Purdue Global: What It Is & How It Works
- Excel Transpose Table: Switch Rows and Columns
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: How to Delete Column and Cell Ranges SafelyTo delete entire columns with Excel VBA, use a worksheet-qualified range such as ws.Columns("B:D").Delete. To delete only cells…
- Excel Macro to Protect a Sheet With a PasswordTo protect the active worksheet with a password in Excel for Microsoft 365 on Windows, run a VBA…
- Excel VBA Message Box: How to Use MsgBox in MacrosIn Excel VBA, use the MsgBox function to show a message, optionally display buttons or an icon, and…
- Excel VBA: Find a Row with Specific Text in a ColumnTo find a row containing specific text in Excel VBA, use the range’s Find method, then read the…
- Access VBA For Loop: Syntax, Examples, and Common ErrorsIn Access VBA, use a For...Next loop to repeat instructions a set number of times, and use For…
- Create a Toggle Button in Excel VBA: Setup and CodeTo create a toggle button in Excel VBA on Windows, choose Developer > Insert > Toggle Button under…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.