VBA & Macro Problems

How to Run an Excel Macro on Multiple Files with One Click

Chanuka GeekiyanageChanuka Geekiyanage Nov 11, 2026 870 views

Question details

The user wants to automate processing multiple Excel workbooks by running a single VBA macro across all of them at once with a single click.

How to Run an Excel Macro on Multiple Files with One Click
Product
Excel
Device & OS
not provided
Scenario
Batch processing multiple Excel files to save time instead of manually opening and running the macro individually on each file.
Observed behavior
The user needs a VBA script or method to sequentially open, process, save, and close an entire folder of workbooks or a selected group of files.
Before you start

Before running any batch processing macro, ensure you create a backup copy of all the target files in a separate folder, as macro actions and bulk file modifications typically cannot be undone.

Solution 1Recommended

Create a Master Macro to Loop Through a Folder

Use a VBA loop combined with the Dir function to iterate through all files in a specific directory, applying your macro code to each.

This is the most common and efficient method for batch processing. You create a 'Master' workbook containing the VBA loop, which targets a specific folder, opens each Excel file, executes your processing code, and closes the file.

1
Open the VBA Editor

Open a new Excel workbook (your Master workbook) and press Alt + F11 to launch the VBA Editor.

2
Insert a New Module

Click 'Insert' from the top menu and select 'Module' to create a blank script window.

3
Write the Looping Script

Write a VBA sub that defines your target folder path. Use 'Filename = Dir(FolderPath & "*.xlsx")' to start the loop. Within a 'Do While Filename <> ""' loop, add code to open the workbook.

4
Call Your Macro and Save

Inside the loop, insert the call to your specific data processing macro. Follow it with 'ActiveWorkbook.Close SaveChanges:=True' to save the changes, and 'Filename = Dir()' to move to the next file.

5
Assign to a One-Click Button

Go back to your Master Excel sheet, navigate to the 'Developer' tab, click 'Insert', and choose 'Button'. Assign your newly created looping macro to this button so you can trigger the batch process with one click.

Create a Master Macro to Loop Through a Folder
Specify Sheet References: Make sure your processing code specifies exactly which worksheet and range to act on (e.g., using ActiveWorkbook.Sheets(1)) so it doesn't accidentally run on your Master workbook.

Batch Process Files using Macros in WPS Spreadsheet

WPS Office provides robust support for VBA and macros, allowing you to run batch processing scripts across multiple workbooks smoothly. It is highly compatible with Microsoft Excel's VBA syntax, making it easy to automate your workflow.

  1. 1. Launch WPS Spreadsheet: Download and open WPS Office, then start a new or existing WPS Spreadsheet document.
  2. 2. Access the Developer Tools: Navigate to the 'Developer' tab on the ribbon and click 'Macro' or press Alt + F11 to open the VBA Editor.
  3. 3. Import or Write Your Script: Paste your existing batch-processing Excel VBA script into a new module.
  4. 4. Add a Form Button: Use the 'Insert' option under the Developer tab to place a Form Control button and assign your macro to it for one-click execution.
Fully compatible with Microsoft Excel's .xlsm and .xlsb macro-enabled formatsSupports standard VBA syntax, user forms, and Developer toolsLightweight and fast, ideal for handling large batch operations without crashingFamiliar user interface for seamless transition and macro management
QA img-9

Frequently Asked Questions

Can I run a macro on multiple files without visually opening them?

VBA generally requires opening the workbook in the background to modify its contents. However, you can use 'Application.ScreenUpdating = False' at the beginning of your code to hide the opening and closing process, which makes the macro run much faster and appear invisible to the user.

How do I let the user choose which files to process instead of a whole folder?

Instead of hardcoding a folder path with the Dir function, you can use 'Application.FileDialog(msoFileDialogFilePicker)' in your VBA code. This prompts the user with a dialog box to manually select multiple files. You can then loop through the 'SelectedItems' collection.

Why does my batch macro crash halfway through the folder?

This often happens if the macro encounters a corrupted file, a password-protected workbook, or an unexpected file format. Add error handling such as 'On Error Resume Next' before the opening step, or write logic to verify the file extension and skip problematic files so the loop can continue.