VBA Loop Through Worksheets: Examples and Tips

To loop through every worksheet in an Excel workbook, declare a Worksheet variable and use For Each ws In ThisWorkbook.Worksheets. Put the code you want to run between that line and Next ws; for example, you can print each sheet’s name or process its cells. In Excel for Microsoft 365 on Windows, place the macro in a standard VBA module and run it after saving a copy of the workbook.

The basic worksheet loop

A For Each loop visits each item in a collection. A workbook’s Worksheets collection contains its worksheets, so the loop variable should be declared as a Worksheet. This simple macro prints every worksheet name to the Immediate window:

Sub ListWorksheetNames()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        Debug.Print ws.Name
    Next ws
End Sub

To try it in desktop Excel for Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the procedure into the module. Press Ctrl+G in the editor to display the Immediate window, then run the macro with F5 while the cursor is inside it. On a Mac, use Option+F11 to open the editor; depending on the keyboard and system settings, you may also need Fn. The VBA editor and macro-running steps differ in Excel for the web: it cannot create, edit, or run VBA macros, so open the workbook in the desktop app.

Before running any macro that changes workbook contents, save a separate copy. VBA changes generally cannot be reversed with Ctrl+Z, so a backup is the practical way to recover from an unintended edit.

Choose the workbook and collection deliberately

ThisWorkbook means the workbook that contains the running VBA code. It is usually the safest choice when a macro is stored in the file it should process. By contrast, ActiveWorkbook means the workbook that is currently active in Excel. If the macro is stored in Personal.xlsb or an add-in, the active workbook may be the file you intend to process—but it can change when a user clicks another workbook. Choose explicitly rather than assuming these names mean the same thing.

For example, if the macro is in a controller workbook and should work on whichever file is active, you can set a workbook variable first. Check that the active workbook is the intended file before running the procedure:

Sub ListActiveWorkbookSheets()
    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ActiveWorkbook
    If wb Is Nothing Then Exit Sub

    For Each ws In wb.Worksheets
        Debug.Print wb.Name, ws.Name
    Next ws
End Sub

The Worksheets collection includes worksheet tabs, including hidden worksheets, but not chart sheets. The broader Sheets collection includes both worksheets and chart sheets. Use Worksheets when your code needs worksheet properties such as cells or ranges. A variable declared as Worksheet cannot represent a chart sheet; if you loop through Sheets, use an object type that can represent either kind and check the sheet’s type before using worksheet-only members.

Process data on each worksheet

Inside the loop, refer to the current sheet through ws. Qualifying a range this way is important: ws.Range("A1") always refers to cell A1 on the current worksheet, whereas an unqualified Range("A1") can refer to the active sheet. That difference can make a macro silently read from or write to the wrong tab.

This example writes a status into B1 on every worksheet whose A1 cell contains the word Ready. It does not activate or select any sheet:

Sub MarkReadySheets()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        If ws.Range("A1").Value = "Ready" Then
            ws.Range("B1").Value = "Checked"
        End If
    Next ws
End Sub

For a real workbook, decide what should happen when A1 contains an Excel error value, a number, or a formula result rather than text. A direct comparison is appropriate for this simple illustration, but data with mixed types may require a check such as IsError or IsNumeric before comparing. Also decide whether text matching should be case-sensitive; VBA’s comparison behavior can be influenced by the module’s Option Compare setting.

Skip sheets or handle only visible tabs

Many workbooks contain instruction, summary, or template sheets that should not be processed. Test the sheet name inside the loop to exclude one or more known tabs. For example, this code prints all names except Summary and Instructions:

Sub ListDataSheetNames()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Summary" And ws.Name <> "Instructions" Then
            Debug.Print ws.Name
        End If
    Next ws
End Sub

Names must match the tab names exactly, including spaces. If users can rename tabs, a fixed-name exclusion can become unreliable; consider marking sheets with a known cell value or maintaining a deliberate list of sheets to include. Hidden worksheets remain in the collection and are processed by the basic loop. To process only visible worksheets, add a condition:

Sub ListVisibleSheets()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            Debug.Print ws.Name
        End If
    Next ws
End Sub

Excel has more than one hidden state: xlSheetHidden is hidden through the normal interface, while xlSheetVeryHidden cannot be unhidden from the standard Unhide dialog. Both are excluded by the visible-only test. Do not change a sheet’s visibility simply to avoid processing it unless altering the workbook is actually intended.

Control the order and handle changes safely

A For Each loop follows the collection’s existing order, which is generally the left-to-right order of sheet tabs. It does not sort sheets alphabetically. If order matters, either arrange the tabs in the required order or use an index-based loop and explicitly determine the sequence. For example, a numeric loop can access each worksheet by position:

Sub ListSheetsByPosition()
    Dim wb As Workbook
    Dim i As Long

    Set wb = ThisWorkbook
    For i = 1 To wb.Worksheets.Count
        Debug.Print i, wb.Worksheets(i).Name
    Next i
End Sub

Avoid adding, deleting, or moving sheets while iterating the same collection unless the code is designed for that change. Altering a collection during a For Each loop can cause confusing results, such as skipping an item or encountering an error. If a task requires deleting several sheets, identify the targets first, then delete them using an index loop that runs from the last worksheet toward the first. Deleting from the end avoids shifting the positions of items that have not yet been checked, but it still deserves a backup and careful confirmation.

Common errors and practical safeguards

A loop that runs but affects the wrong workbook is often using ActiveWorkbook when ThisWorkbook was intended, or the reverse. A loop that reads from the wrong tab often has an unqualified Range or Cells reference. Qualify worksheet references with ws, and qualify workbook references where there could be ambiguity.

Other useful safeguards include:

Check the target first: Print sheet names to the Immediate window before adding code that changes cells, formatting, or sheet structure.
Expect missing or unusual data: Blank cells, error values, merged cells, and formulas returning empty text can affect assumptions about a sheet’s contents.
Use the right object type: A Worksheet variable works with worksheets, not chart sheets.
Do not assume a fixed sheet count: Use Worksheets.Count when indexing so the code adapts when tabs are added or removed.
Keep the original safe: Save a copy before a macro makes broad edits or deletes anything; Ctrl+Z does not undo normal VBA actions.

If a macro stops with an error, note the highlighted line and check the object being referenced. A range error may point to an invalid address or a sheet that is not the expected type. A type mismatch may mean a cell contains an error value or a different data type than the code anticipates. Avoid adding a broad On Error Resume Next merely to make an error disappear; it can hide the cause and allow later code to continue with incorrect results.

Improve speed for larger workbooks

Looping through a modest number of worksheets is normally straightforward. A macro can become slow when it repeatedly reads or writes individual cells across many sheets, especially if each operation triggers recalculation, events, or screen updates. Where possible, read a block of cells into a VBA array, process the values in memory, and write the results back in a block. The right approach depends on the size and layout of the data; a cell-by-cell loop is often easier to understand for a small, fixed task.

For a long-running macro, developers sometimes temporarily disable screen updating or automatic calculation and restore the previous settings afterward. These are application-wide settings, so error handling matters: if the procedure exits early without restoring them, Excel can appear frozen or stop recalculating automatically. Save the existing settings, restore them in a cleanup section, and test the macro on a copy. Do not add performance settings to a short loop unless there is a measured need, because they introduce extra state to manage.

When VBA is and is not the right tool

VBA is useful when the same workbook task needs to be repeated, when a loop must apply conditional logic across many tabs, or when the process needs to interact with workbook objects such as sheet names, visibility, and formatting. A small macro can make a repetitive operation consistent, but it requires a macro-enabled workbook format such as .xlsm if the VBA code needs to be retained in the file. Macro security settings may also restrict running code, particularly when a file came from an untrusted source.

For a one-time operation, Excel’s built-in interface may be simpler and easier to review. For example, grouping selected worksheet tabs lets you enter the same content or formatting on multiple sheets at once, but grouped sheets are easy to overlook: edits then affect every grouped sheet. For consolidating structured data, Power Query can combine tables without a VBA loop and can be refreshed later. Choose VBA when the task genuinely needs programmable, sheet-by-sheet control; choose a built-in feature when it accomplishes the job more transparently.

Frequently Asked Questions

How do I loop through all worksheets in the active workbook?
Set a workbook variable to ActiveWorkbook, then use For Each ws In wb.Worksheets. Confirm the active workbook is the intended file before running the macro, especially if the code is stored in Personal.xlsb or an add-in.

Does a worksheet loop include hidden sheets?
Yes. For Each ws In wb.Worksheets includes visible, hidden, and very hidden worksheets. Check ws.Visible = xlSheetVisible inside the loop if you want to process only visible tabs.

What is the difference between Worksheets and Sheets in VBA?
Worksheets contains worksheet tabs. Sheets contains worksheets and chart sheets. Use Worksheets when the procedure needs cells, ranges, or other worksheet-specific members.

How can I skip a worksheet by name?
Test ws.Name in an If statement inside the loop, and put the work inside the condition. Make sure the name matches the tab exactly; if tabs are renamed regularly, use a more stable way to identify sheets.

Can I run VBA loops in Excel for the web?
No. Excel for the web can preserve a workbook containing VBA, but it cannot create, edit, or run VBA macros. Open the file in the desktop version of Excel to use the Visual Basic Editor and run the procedure.

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.