Beta

This is a new feature and we can’t wait to hear your feedback. Is it useful for your tasks? Is it easy to use? Just let us know via website or email us.

Use with caution: beta features may contain some bugs and rough edges. To test the feature, take a free trial or update XLTools.

Record and run the same macro on multiple Excel workbooks without coding

(Note: the video may not reflect the latest upgrades. Please use the guide below.)

Quite often you have to work with standardized Excel files: regular reports, invoices, orders from regional offices, etc. Sometimes you end up with 200, 300, or even 1000 uniform spreadsheets with similar structure. Now, what if you need to change a value in a specific cell, or delete a row, or replace some text across all the workbooks? How do you push the same changes to each workbook automatically?

XLTools Loop Macro feature helps you push changes to many workbooks at once:

  • Record the changes as a macro
  • Run the macro on multiple workbooks without opening them
  • Does not require VBA coding skills

Before you begin, add Loop Macro Format to Excel

Loop Macro is one of the 20+ features within XLTools Add-in for Excel. Works in Excel 2019, 2016, 2013, 2010, desktop Office 365.

Get started with XLTools ribbon
– free trial gives you 14 days of full access to all features.

How to loop macro through multiple workbooks

The Loop Macro feature helps loop a macro through multiple Excel workbooks, in other words, helps run the same macro on many workbooks.

Note: before you proceed, we recommend you back up the files. Once you execute a macro, the changes cannot be undone.
  1. Open one of the workbooks, where you want to make the changes. It will serve as a template.
  2. Open XLTools tab In the Automation group, click Loop Macro.

    Click the Loop Macro button on XLTools ribbon
  3. In the Loop Macro window, select the macro you want to execute:

    • If your workbook contains custom macros, select a macro from the list.

      Limitation: macros from a Personal Macro Workbook are not available.
    • Select the newly recorded macro – see how to record a new macro.
    Select the macro to loop through Excel workbooks
  4. To select files, click the Browse icon Select the folder with the files you want to loop through The files will be added to the list If necessary:

    • Check the box Include files in subfolders, and the files will be added
    • Uncheck the boxes for the files you want to exclude from processing
    Select the workbooks to run the macro against
  5. Click Run Wait while the macro loops through all of the selected workbooks.

    Note: We’ve tried to make the processing as fast as possible. It runs in the background without opening the workbooks. Processing time depends on the number of files to process, file size, the extent of changes, and your computer processing powers.
  6. Review the result for each file, it is provided in the Status column:

    • “done” – the macro was executed successfully
    • “not processed” – the macro was not executed
    • “error” – the macro failed with an error

    To open a file, select it Right click Choose to Open this file or Open containing folder.

    Review results of the macro executed on all files in a folder

How to record a macro on the fly with the Loop Macro feature

The Loop Macro feature is integrated with Excel's native Macro Recorder. You can record a macro on the fly and then loop it through multiple workbooks.

  1. Open XLTools tab In the Automation group, click Loop Macro A window will open Click the Record new macro icon.

    Step 1: record a new macro
  2. In the Record Macro window, name the macro:

    • Assign a descriptive name to the macro
    • The option “Store macro in this workbook” will be selected by default
    • Adding a shortcut key and description is optional
    Step 2: name the macro rebore recording
  3. Click OK The macro will begin recording Make the necessary changes you want to record.
  4. When ready, click the Stop Recording button on XLTools ribbon.

    Or: use the keyboard shortcut combination Shift + Ctrl + M.
    Step 3: stop recording macro
  5. When you stop recording, the Loop Macro window will open again. Advanced users can edit the newly recorded macro in the Visual Basic Editor.

    Step 4: edit macro if necessary

How to push the same changes to multiple workbooks [example]

The Loop Macro feature allows you to make the same changes to all workbooks in a folder at once.

Let’s say you have a folder with about 200 workbooks. Each workbook contains monthly orders from one regional office, for example “North America_January”. The workbooks are structured identically. You want to add the calculation of total amounts to each workbook automatically.

Tip: if you have planned a long list of changes to the workbooks, we recommend you split the process into multiple steps and record smaller macros instead of one long maro.
  1. Back up the files before you proceed.
  2. Open one of the workbooks that will serve as a template, e.g. “North America_January”.
  3. Open XLTools tab In the Automation group, click Loop Macro A window will open.
  4. Click the Record new macro icon Give the macro a descriptive name, e.g. “AddTotalAmount” Click OK The macro is now being recorded, so make the necessary changes:

    • Add text in E1: “TOTAL:”
    • Add the formula in cell E2: =SUM(C:C)
    • Apply Green Data Bar conditional formatting to column C. This will visualize the relative amount of the order.
    Loop Macro example: record changes
  5. Сlick the Stop Recording button on XLTools ribbon The Loop Macro window will open again.
  6. Select the folder with all the files where you want to push the same changes See all the files added to the list.

    Loop Macro example: select workbooks
  7. Click Run Wait a few minutes for the processing to complete Done, all the workbooks are successfully processed.

    Loop Macro example: all workbooks are processed
  8. Open one of the processed workbooks, e.g. “Europe_April” and see that the changes were applied correctly and as intended.

    Loop Macro example: review the result of the macro executed
Any questions or suggestions?
How can we help?