VBA & Macro Problems

How to Find and Replace Values in Multiple Excel Workbooks Using VBA

Olivia Miller Oct 15, 2026 870 views

Question details

The user needs a VBA macro to batch process find-and-replace tasks across approximately 100 Excel files in a folder based on a list of value pairs.

Find and Replace Values in Multiple Excel Workbooks with VBA

Product: Excel
Device & OS: not provided
Scenario: Updating data in bulk across many Excel files using a predefined list of find-and-replace pairs.
Observed behavior: A macro is required to read the pairs, loop through the target folder, apply replacements on all sheets, and save the updated workbooks efficiently.

Before you start

Verify that your list of find-and-replace pairs is accurate and ensure all target workbooks are backed up in a separate folder before running bulk VBA scripts.

Solution 1Recommended

Create a VBA Macro to Process Multiple Workbooks

Use a VBA script to read find-and-replace pairs from a master sheet and loop through all Excel files in a selected folder.

The macro will read the find-and-replace pairs from a specific worksheet (e.g., 'Sheet1' or 'List') starting from columns A and B. It will then prompt you to select a folder and process every workbook inside it.

Note that the VBA Replace method may return True even if no replacement occurs, meaning workbooks might be saved even without actual changes. Counting matches separately can prevent this but may slow down the macro processing time.

Open the VBA Editor

Launch Excel and press ALT + F11 to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

Click on Insert in the top menu, then select Module to create a blank workspace for your code.

Set Up the Target Folder Prompt

Write your script using Application.FileDialog(msoFileDialogFolderPicker) so the macro prompts you to select the folder containing the 100 Excel files.

Loop Through Data Pairs and Sheets

Create a loop in the code to read the find-and-replace pairs from your master sheet (e.g., Column A for 'Find', Column B for 'Replace'), and apply the Cells.Replace method to every worksheet in each opened workbook.

Save and Close

Add commands to save and close each workbook automatically after the loop finishes processing the sheets.

Create a VBA Macro to Process Multiple Workbooks

Worksheet Naming: Ensure the worksheet name referenced in your VBA code exactly matches your master sheet (e.g., changing the constant to 'Sheet1' often resolves object referencing errors).

Efficient Bulk Editing

Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Office provides robust support for VBA macros, allowing you to run your batch find-and-replace scripts across multiple workbooks effortlessly.

1. Open WPS Spreadsheet: Launch WPS Spreadsheet and create a new workbook to store your find-and-replace macro and data pairs.
2. Access the Developer Tools: Navigate to the Developer tab and click on the Visual Basic icon to open the VBA editor.
3. Insert the Macro Code: Insert a new module and paste your batch find-and-replace VBA script.
4. Run the Batch Process: Run the macro and select your target folder to automatically process the workbooks.

Native support for Excel VBA macros and .xlsm filesFast processing of multiple workbooks simultaneouslyHighly compatible with Microsoft Excel formats, formulas, and functionsLightweight application that runs smoothly even with large data tasks

microsoft office alternative - wps office

Frequently Asked Questions

Can I prevent the macro from saving workbooks that didn't need any replacements?

Yes, but relying on the Replace method's boolean return or the workbook's Saved property is unreliable. You must use a separate function to count occurrences of the search term before applying the replacement to accurately determine if saving is necessary.

Why am I getting an error that the worksheet cannot be found?

This usually happens if the constant or variable in your VBA code references a sheet name that doesn't exist. Ensure your code specifically matches the exact name of your active sheet, such as 'Sheet1' or 'List'.

Does the Replace method work on hidden sheets in VBA?

By default, the Cells.Replace method will process hidden sheets if your VBA script loops through all worksheets in the workbook and applies the method to each one individually.

How do I specify the folder containing my Excel files dynamically?

You can use the Application.FileDialog(msoFileDialogFolderPicker) object in your VBA script to prompt the user with a dialog box to select the target folder when the macro runs.

Excel & Data Problems