How to Combine Data from Multiple Excel Files into One Worksheet Using VBA
Question details
The user needs a VBA macro to merge data from multiple Excel workbooks located in a specific folder into a single destination worksheet, ensuring that the shared header row is imported only once.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating data from more than 20 separate Excel files into a master sheet without manually copying and pasting.
- Observed behavior
- The user requires an automated routine that prompts for a folder selection, loops through each file, copies the used data range, and appends it to the master worksheet while intelligently skipping the header row on subsequent files.
Ensure all the source Excel files are placed in a single, accessible folder and that they share the exact same column structure and header layout. Save your current master workbook as a Macro-Enabled Workbook (.xlsm) to allow VBA execution.
Use a VBA Macro to Loop and Merge Multiple Workbooks
This solution provides the steps to implement a VBA script that prompts for a folder, loops through all files, and appends data while handling the header row.
By utilizing the FileDialog object in VBA, you can dynamically select a folder containing your source files. The macro will then iterate through every Excel file in that folder, opening them one by one. To prevent duplicate headers, the script copies the header row from the first file and then dynamically skips the first row for all subsequent files before pasting the used range into the master worksheet.
In your master Excel workbook, press ALT + F11 to open the Visual Basic for Applications (VBA) editor.
Click on 'Insert' in the top menu bar, then select 'Module' to create a blank workspace for your script.
Paste your VBA code into the module. Ensure your code includes 'Application.FileDialog(msoFileDialogFolderPicker)' to let you choose the source directory, and a loop using the 'Dir' function to cycle through each file. Use a boolean variable (e.g., 'HeaderCopied') to track if the first row has been imported.
Press F5 or click the 'Run' button (the green triangle) on the toolbar. When the file dialog appears, select the folder containing your 20+ Excel files and click 'OK'.
Merge Multiple Excel Files Easily with WPS Spreadsheet
WPS Office provides excellent support for VBA macros, allowing you to seamlessly run complex Excel consolidation scripts. You can easily merge multiple workbooks into a single master sheet using WPS Spreadsheet without needing to modify your existing VBA code.
- 1. Create a Master Workbook: Open WPS Spreadsheet and create a new blank workbook to serve as your destination file.
- 2. Access the VBA Editor: Navigate to the 'Developer' tab on the ribbon and click on 'VBA Editor' to launch the macro environment.
- 3. Insert and Run the Macro: Insert a new Module, paste your folder-looping consolidation script, and press F5 to run it.
- 4. Select the Source Folder: Choose the folder containing your data files when prompted, and let WPS Spreadsheet merge the data instantly.
Frequently Asked Questions
Can I merge files with different file extensions, such as .xls and .xlsx?
Yes. You can modify the VBA Dir() function argument to look for '*.xls*' so that it captures both older .xls files and newer .xlsx or .csv files located in the same folder.
How do I modify the VBA code to copy from a specific sheet name instead of the first sheet?
Within your VBA loop, change the worksheet index reference from Sheets(1) to Sheets("YourSheetName"). This ensures the macro only copies data from that specifically named worksheet inside every source file.
Why is my macro copying blank rows into the master sheet?
This happens if the 'UsedRange' property in your source files extends beyond the actual visible data due to previous formatting. You can fix this by calculating the actual last row using 'Cells(Rows.Count, 1).End(xlUp).Row' instead of relying entirely on the UsedRange property.
Do I need to keep the source files open after running the macro?
No. A properly structured VBA macro will use the 'Workbook.Close False' command to automatically close each source file immediately after its data has been copied, keeping your system memory free.
