How to Update Formulas in Multiple Excel Workbooks Using a Macro
Question details
The user needs to learn how to batch update or replace formulas across multiple Excel files simultaneously using automation.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- The user manages many Excel files based on the same template and needs to apply consistent formula updates to all of them without opening each file manually.
- Observed behavior
- The goal is to automate the repetitive process of opening, editing, and saving multiple workbooks so that formula changes can be applied programmatically across an entire folder.
Before running any VBA macros, ensure you have backed up your target Excel folder to prevent accidental data loss, and verify that all workbooks in the folder share the exact same sheet and cell structure.
Use a VBA Macro to Batch Update Formulas in a Folder
A VBA script can automatically loop through all Excel files in a designated directory, apply new formulas to specified cells, and save the changes instantly.
This method utilizes a simple loop to open each workbook, update the target cells, and close the file with saved changes. It significantly reduces manual effort when dealing with dozens or hundreds of template-based files.
Open Excel, go to File > Options > Customize Ribbon, and check the 'Developer' box in the right pane to enable macro tools.
Press Alt + F11 to open the Visual Basic for Applications editor. Go to Insert > Module in the top menu to create a new blank script window.
Copy and paste the following batch update code into the module window: Sub UpdateFormulas() Dim folderPath As String, fileName As String, wb As Workbook Application.ScreenUpdating = False folderPath = "C:\Excel\" fileName = Dir(folderPath & "*.xls*") Do While fileName <> "" Set wb = Workbooks.Open(folderPath & fileName) wb.Worksheets(1).Range("A20").Formula = "=SUM(A1:A19)" wb.Close SaveChanges:=True fileName = Dir Loop Application.ScreenUpdating = True End Sub
Modify the 'folderPath' variable to match the exact folder directory where your files are stored. Additionally, update the 'Range("A20")' and the formula string to suit your specific data requirements.
Press F5 or click the green 'Run' button in the VBA toolbar. Wait a few moments as Excel processes and updates every workbook in the specified folder in the background.
Batch Update Formulas Using WPS Spreadsheet Macros
WPS Spreadsheet fully supports VBA macros, allowing you to run the exact same batch update scripts seamlessly. Automate repetitive tasks and manage multiple workbooks efficiently with a powerful, lightweight suite.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a new blank spreadsheet document.
- 2. Access Developer Tools: Navigate to the 'Tools' tab on the ribbon and click on 'Developer' to reveal the Macro and Visual Basic editor options.
- 3. Run the Automation Script: Open the VBA Editor (Alt+F11), insert a new module, paste your batch update code, and press F5 to apply the formula changes across your targeted folder.
Frequently Asked Questions
Can I update formulas across different sheets within the multiple workbooks?
Yes, you can modify the VBA code to specify the exact sheet name (e.g., wb.Worksheets("SalesData").Range...) instead of using the numerical sheet index. This ensures the macro updates the correct sheet even if the worksheet order varies between files.
Why is the macro running slowly when updating many files?
Running macros can be slow if Excel recalculates formulas or refreshes the screen for every file it opens. Adding 'Application.ScreenUpdating = False' and 'Application.Calculation = xlCalculationManual' at the beginning of your script will significantly speed up the processing time.
Will this macro work if my Excel files are password protected?
No, if the workbooks are password protected, the macro will pause and prompt for the password for every single file. You can bypass this by adding the password parameter directly in the open command: Workbooks.Open(folderPath & fileName, Password:="yourpassword").
