Merge Two Excel Workbooks with VBA Using Multiple Columns
Question details
The user needs to merge data from a donor workbook into a parent workbook based on three matching criteria columns.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating the consolidation of records from two separate workbooks where matches are determined by evaluating three specific columns simultaneously.
- Observed behavior
- When a match is found, a new row should be inserted beneath the matching parent record to hold the copied donor information and criteria.
Ensure both the parent and donor workbooks are saved in a macro-enabled format (.xlsm) and identify the exact sheet names and file paths to be referenced in the script before running the code.
Use a VBA Macro to Match and Merge Workbooks
Write a VBA macro that opens the donor file, loops through both datasets, compares the three criteria columns, and dynamically inserts rows for matches.
Because inserting rows dynamically shifts the remaining data downward, the macro must adjust the row counters during the loop to prevent missing any records.
You must update the placeholder file paths, sheet names, and column letters in the code to match your actual workbook structure.
Open the parent workbook in Excel, press ALT + F11 to open the VBA Editor, and click Insert > Module from the top menu.
Copy your workbook merging script (e.g., Sub MergeFiles) and paste it into the new blank module window.
Modify the code to replace "C:\path\to\DonorFile.xlsx" with your actual donor file path, and update "ParentSheetName" and "DonorSheetName" to match your specific worksheets.
Check the column references in the script (such as columns A, B, and C for the three criteria, and D, E for the copied donor info) and change the letters if your data layout is different.
Close the VBA Editor, press ALT + F8 to open the Macro dialog box, select MergeFiles, and click Run to execute the automated merge.
Automate Data Merges with WPS Spreadsheet
WPS Office provides robust support for VBA macros (in the Pro version), allowing you to run complex scripts like multi-column workbook merges effortlessly. It offers a familiar interface and excellent compatibility with Microsoft Excel.
- 1. Download and Install WPS Office: Download and open WPS Office, then launch the WPS Spreadsheet application.
- 2. Access the Developer Tab: Open your parent workbook and navigate to the Developer tab (requires WPS Pro for VBA support).
- 3. Insert the VBA Code: Click on 'Macros' or the 'VBA Editor' icon, insert a new module, and paste your workbook-merging VBA code.
- 4. Execute the Merge: Run the macro to instantly consolidate your donor data based on the three criteria columns.
Frequently Asked Questions
Why is the macro skipping rows after inserting a matched record?
When a row is inserted (Shift:=xlDown), the index of all subsequent rows increases. Your VBA code must increment the row counter variables (e.g., lastRowParent = lastRowParent + 1 and i = i + 1) immediately after insertion to account for the shift and avoid skipping the next record.
Can I use this VBA script to match on more or fewer columns?
Yes. You can modify the script by adding or removing criteria variables (like parentCriteria4) and updating the If statement to include them (e.g., And parentCriteria4 = donorCriteria4).
How do I prevent the screen from flickering while the macro runs?
Add Application.ScreenUpdating = False at the beginning of your script and Application.ScreenUpdating = True at the end. This stops the screen from refreshing during the loop, which also significantly speeds up the execution time.
What happens if the donor workbook is already open?
If the donor workbook is already open, the Workbooks.Open command might cause an error or prompt a read-only warning. You can add an error-handling routine or check if the workbook is already open using a custom VBA function before executing the open command.
