Beta
This is a new feature and we can’t wait to hear your feedback. Just let us know. Use with caution: beta features may contain some bugs and rough edges. To test the feature, start a free trial or update to the latest XLTools version.
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:
- 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.
How to enable access to macros and start using the Loop Macro tool
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.
Macro Security 
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.
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.
- Open one of the workbooks, where you want to make the changes. It will serve as a template.
- Select the newly recorded macro – see how to record a new macro.
- 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
- “done” – the macro was executed successfully
- “not processed” – the macro was not executed
- “error” – the macro failed with an error
Open XLTools tab In the Automation group, click Loop Macro.
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.
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:
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.
Review the result for each file, it is provided in the Status column:
To open a file, select it Right click Choose to Open this file or Open containing 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.
- 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
- Click OK The macro will begin recording Make the necessary changes you want to record.
Open XLTools tab In the Automation group, click Loop Macro A window will open Click the Record new macro icon.
In the Record Macro window, name the macro:
When ready, click the Stop Recording button on XLTools ribbon.
Or:use the keyboard shortcut combination Shift + Ctrl + M.
When you stop recording, the Loop Macro window will open again. Advanced users can edit the newly recorded macro in the Visual Basic Editor.
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.
- Back up the files before you proceed.
- Open one of the workbooks that will serve as a template, e.g. “North America_January”.
- Open XLTools tab In the Automation group, click Loop Macro A window will open.
- 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.
- Сlick the Stop Recording button on XLTools ribbon The Loop Macro window will open again.
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:
Select the folder with all the files where you want to push the same changes See all the files added to the list.
Click Run Wait a few minutes for the processing to complete Done, all the workbooks are successfully processed.
Open one of the processed workbooks, e.g. “Europe_April” and see that the changes were applied correctly and as intended.
Any questions or suggestions?
FCDoes the process work for csv files too? I can't find the way to populate the list of files to apply the macro on when the folder only contains csv files.
Nair KhanGood Day !!! When i record Micro Loop on other sheets it gives me error. I try 100 times but fail, pls help me Pls share your email so I can send you my sheet also ....
