VBA & Macro Problems

Combine Multiple Excel Files into One Workbook Using VBA

WPS Content Manager Sep 27, 2026 870 views

Question details

The user wants to import a specific worksheet from more than 20 Excel files into a single master workbook, remove duplicate rows, and autofit column widths.

How to Combine Multiple Excel Files into One Workbook Using VBA

Product: Excel
Device & OS: not provided
Scenario: Consolidating data from multiple source files into a single master spreadsheet for reporting or analysis.
Observed behavior: The source files are currently separate and require a VBA macro to automate the merging, deduplication, and formatting process into a master file.

Before you start

Ensure all source Excel files are saved in the same folder and share the exact same column structure and headings. It is highly recommended to test the VBA macro on copies of your files to prevent accidental data loss.

Solution 1Recommended

Create and Run a VBA Macro to Merge Workbooks

Use a custom VBA script to prompt for a folder, extract the target worksheet, merge the data, and automatically clean it up.

This VBA approach will open each file in a specified folder, copy the desired worksheet data, append it to a master sheet, remove duplicate rows, and autofit the columns.

Open the VBA Editor

Open your master workbook and press Alt + F11 to launch the Visual Basic for Applications (VBA) Editor.

Insert a New Module

Click on Insert > Module in the top menu to create a blank workspace for your macro.

Add the Consolidation Code

Write or paste your VBA code designed to prompt for a folder (using FileDialog), loop through each Excel file, open them, and copy the worksheet data (e.g., 'Daily Rates') into your master workbook.

Add Cleanup Functions

Include code to remove duplicates using the Range.RemoveDuplicates method and adjust columns using Columns.AutoFit before ending the script.

Run the Macro

Close the VBA Editor, press Alt + F8 in your master workbook, select your newly created macro, and click Run.

Create and Run a VBA Macro to Merge Workbooks

Data Privacy: If your macro does not work as expected and you need to share a sample file for troubleshooting, ensure you remove all sensitive or private information first.

Efficiently Handle Macros with WPS Office

Combine Multiple Spreadsheets with WPS Spreadsheet Macros

WPS Spreadsheet fully supports VBA and Macros, allowing you to easily automate tasks like merging dozens of workbooks, removing duplicates, and formatting data in just a few clicks.

1. Enable the Developer Tab: Open WPS Spreadsheet and navigate to the Developer tab. If it is not visible, you can enable it in the software settings.
2. Open the Macro Editor: Click on the 'Macros' or 'Visual Basic' button to open the built-in WPS Macro Editor.
3. Insert Code and Save: Insert a new Module, paste your file consolidation macro code, and save the file as a Macro-Enabled Workbook (.xlsm).
4. Execute the Consolidation: Run the macro to automatically merge your targeted files into one master sheet and automatically clean up duplicates.

Fully compatible with Microsoft Excel (.xlsx, .xlsm, .xls) formats.Robust VBA and Macro support for automating repetitive tasks across multiple files.Built-in tools for removing duplicates and formatting data instantly.Lightweight, fast, and completely free to download and use.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA macro not copying data from all files?

Ensure that the worksheet name specified in your macro perfectly matches the sheet name in the source files (e.g., exactly 'Daily Rates'). Also, verify that all target files are stored in the folder you selected when prompted.

How do I remove duplicates without using VBA?

You can manually remove duplicates by selecting your combined data range, navigating to the Data tab on the ribbon, and clicking the Remove Duplicates button.

What should I do if the macro returns an error during the loop?

Test the macro on a small batch of 2 or 3 dummy files first. Check that all source files share the exact same column structure and headings. Ensure no source files are currently open and locked by another user.

Can I run this VBA macro on a Mac?

While basic VBA is supported on Mac versions of Office, macros that interact heavily with the file system (like prompting for a folder path) often require different code syntax due to the differences between Windows and macOS directory paths.

Excel & Data Problems