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

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:

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

Download XLTools for Excel

To use XLTools Loop Macro tool, you need to enable access to macros in your Excel settings:

Developer tab Macro Security Check "Enable all macros" and "Trust access to the VBA project object model" OK Restart Excel.

Or: File Options Trust Center Trust Center Settings Macro Settings Check "Enable all macros" and "Trust access to the VBA project object model" OK Restart Excel.

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.

   

   

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.

   

   

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

   

   

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.

   

   

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.

   

   

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

   

   

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.

   

   

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.

   

   

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.

   

   

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.

   

   

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

   

   

8. Open one of the processed workbooks, e.g. “Europe_April” and see that the changes were applied correctly and as intended.