Excel VBA: Loop Through All Open Workbooks in a Macro

To loop through every workbook currently open in desktop Excel, use a For Each loop over Application.Workbooks. Declare a Workbook variable, then put the work you want to perform inside the loop; the example below prints each open workbook’s name in the Immediate window.

Sub ListOpenWorkbooks()
    Dim wb As Workbook

    ' Visit each workbook open in this Excel instance.
    For Each wb In Application.Workbooks
        Debug.Print wb.Name
    Next wb
End Sub

This pattern is useful for checking a group of files, gathering information, or applying the same operation across several workbooks without opening them one at a time. Before running a macro that changes data, save a copy of the workbook: VBA actions cannot be undone with Ctrl+Z.

How the Workbooks collection works

Application.Workbooks is Excel’s collection of workbook objects open in the current Excel application instance. In a For Each loop, VBA assigns one workbook object at a time to wb. When the loop reaches the end of the collection, it stops. You do not need to know the number of open files in advance, and the collection’s first item is not necessarily the workbook you want to process.

A workbook object represents an open Excel file, not a worksheet. To work with a tab inside that file, use its Worksheets collection, for example wb.Worksheets(1) or a worksheet name. Excel may show more than one window for a workbook, but those windows do not mean there are separate workbook files to process. The loop works with the open workbook objects.

The collection refers only to workbooks available in the Excel instance running the macro. It does not search a folder, find closed files, or automatically open files that are not already open. If a task needs every file in a directory, use a separate file-search and open-workbook approach rather than expecting this loop to find closed workbooks.

Run and adapt the basic loop

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. Choose Insert > Module, paste the procedure into the module, and run ListOpenWorkbooks from the editor. Open the Immediate window with Ctrl+G to see the printed names. The Immediate window is a debugging pane in the Visual Basic Editor; the results are not written into a worksheet.

On a Mac, open the Visual Basic Editor with Option+F11; some keyboards also require the Fn key. The VBA editor and macro support are available in desktop Excel for Mac, although keyboard behavior and some object-model details can differ from Windows. Excel for the web cannot create, edit, or run VBA macros, so use desktop Excel for this task.

wb.Name returns the workbook’s file name, such as Budget.xlsx. To distinguish files with identical names in different folders, print wb.FullName instead; that property includes the path. A workbook that has not yet been saved may not have a meaningful saved path, so code that relies on FullName should account for unsaved files.

Choose which open workbooks to process

One frequent mistake is assuming that the workbook containing the macro is the same as the workbook currently selected in Excel. ThisWorkbook means the workbook where the running VBA code is stored. ActiveWorkbook means the workbook that is currently active in the Excel interface. They can be different—for instance, when a macro is stored in Personal.xlsb or an add-in. By contrast, Application.Workbooks represents the whole open-workbook collection.

If you want to process every open workbook except the one that holds the macro, test the object identity rather than comparing names. This avoids accidentally excluding another file that happens to share the same name.

Sub ListOtherOpenWorkbooks()
    Dim wb As Workbook

    For Each wb In Application.Workbooks
        If Not wb Is ThisWorkbook Then
            Debug.Print wb.FullName
        End If
    Next wb
End Sub

For a specific file, compare a property such as Name or FullName inside the loop. Use a full path when duplicate file names are possible. If you only mean the workbook the user is working in, use ActiveWorkbook directly instead of looping through the entire collection. Being precise about the target matters especially when a macro writes values, changes formatting, or saves files.

Loop through worksheets inside each workbook

A common next step is to look for a particular sheet in every open workbook. Put a second loop inside the workbook loop. This example prints the workbook name and the displayed contents of cell A1 for each worksheet named Summary. It does not change any workbook data.

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

    For Each wb In Application.Workbooks
        For Each ws In wb.Worksheets
            If ws.Name = "Summary" Then
                Debug.Print wb.Name & " | " & ws.Range("A1").Text
            End If
        Next ws
    Next wb
End Sub

The inner loop checks each worksheet’s name, so a missing Summary tab does not cause a subscript error. For workbooks with many sheets, this checks every sheet in turn; for a known sheet that should exist, you can instead refer to it by name and add error handling for files that do not contain it. Worksheets includes worksheet tabs, while Sheets can also include chart sheets. Choose the collection that matches what the macro must handle.

This pattern can be adapted to inspect a cell, count records, or collect a value for a report. Decide what should happen when a workbook is missing the expected sheet or has a different layout. A loop makes repetition easier, but it does not make inconsistent workbooks consistent; checks for the required sheet, range, and data type still belong in the procedure.

Protect files and avoid unintended changes

Start with a read-only pass when developing a macro. Printing names and values to the Immediate window lets you confirm which files and sheets the loop reaches before you add actions that alter them. If the macro will edit cells, add a clear inclusion rule and test it on copies of representative files. Save a backup before running it on important workbooks, because macro changes are not reversed by Ctrl+Z.

Think carefully before adding wb.Save to a loop. Saving every workbook can commit changes to files the user did not expect to change. A workbook may also be read-only, protected, or stored where the user lacks write permission; saving can then fail. If the purpose is to inspect data, leave the files unchanged. If saving is necessary, make the target scope explicit and decide how the code should respond to a failed save.

Also avoid relying on selection, such as ActiveWorkbook, ActiveSheet, or Selection, while iterating. Those references depend on Excel’s current interface state. Use the loop variable explicitly—such as wb.Worksheets—so each action is directed at the workbook currently assigned to wb. This makes the code easier to understand and less likely to act on the wrong file.

Common problems and troubleshooting

The Immediate window looks empty: Open it in the Visual Basic Editor with Ctrl+G on Windows, then run the procedure again. The macro’s output is not automatically placed in a worksheet.
A workbook is unexpectedly included: Remember that every workbook open in the same Excel instance is part of the collection, including the file containing the macro. Add an explicit exclusion or inclusion test.
A sheet reference raises an error: The workbook may not contain that worksheet, or the sheet name may differ. Loop through wb.Worksheets and compare names before using a sheet-specific range.
The macro cannot run: Confirm that you are using desktop Excel and that macros are permitted by the organization’s security settings. Save macro-containing workbooks in a macro-enabled format such as .xlsm when needed.
A file is missing from the results: Check whether it is open in the same Excel instance as the macro. Files open in a separate Excel process are not necessarily part of the current instance’s collection.

When debugging, add a temporary Debug.Print wb.Name line at the start of the loop. It shows which workbook objects the code is reaching. If the list is correct but a later action fails, inspect the specific sheet or range used at that point rather than assuming the collection loop itself is the problem.

When to use a different approach

Use Application.Workbooks when the workbooks are already open and the macro should act on those exact files. If the files are closed and stored in a known folder, you need code that searches the folder and opens files, or another suitable import process. That is a different task: it introduces decisions about file patterns, password prompts, read-only access, and whether to save or close each file afterward.

For a one-off task involving only one workbook, a direct reference such as ActiveWorkbook may be clearer. For a repeatable task across a handful of already-open files, the collection loop is usually straightforward. For data consolidation that does not require custom VBA behavior, Power Query may be a better fit, especially when importing many files from a folder and refreshing the combined result later. The right choice depends on whether the files are open, how uniform their layouts are, and whether the task needs to change source workbooks.

Frequently Asked Questions

How do I loop through all open workbooks in VBA?
Declare a Workbook variable and use For Each wb In Application.Workbooks. Put the operation you want to repeat before Next wb.

Does Application.Workbooks include the workbook with the macro?
Yes, if that workbook is open in the same Excel instance. Use If Not wb Is ThisWorkbook Then to skip the workbook containing the code.

How can I print the names of open workbooks?
Inside the loop, use Debug.Print wb.Name and view the output in the Visual Basic Editor’s Immediate window. Use wb.FullName if you also need the saved file path.

Can VBA loop through workbooks that are closed?
No. Application.Workbooks contains workbooks open in the current Excel instance. To process closed files, the macro must locate and open them or use another file-import method.

Can I run this macro in Excel for the web?
No. Excel for the web does not run VBA. Run the macro in desktop Excel for Windows or Mac, subject to the applicable macro security settings.

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.