Combine Multiple Excel Files Into One Sheet Vba

  1. To run the macro, press the key F5. Data of both sheets will be copied and pasted in an Excel sheet. This is the way we can collate the data in a sheet from different files of Excel through VBA in Microsoft Excel.
  2. VBA to compile and collate Multiple excel workbooks and sheets data in one sheet in new excel file. Question: Hi I’m looking for comment for below scenario. I have 3 excel file named ABC1, ABC2 and ABC3. I want to copy selected range of rows and column (it’s same for all the 3 excel sheet), in to new excel file.

Consolidate data from multiple Worksheets into a single sheet By Row using VBA:Project Objective. VBA to concatenate the data in multiple Worksheets to a newly created Worksheet in the same workbook. The ranges in all worksheets are concatenated into the consolidated Worksheet(final Worksheet) one after another in rows wise.

I have previously shared a post with an example on how to read data from a closed workbook without opening it. I am extending that example, and here in this post I’ll show you how to read multiple excel files or workbooks and merge the data into a single Excel file using VBA.

Let us assume I have two Excel files (source files) with sales data in it. All the files (or workbook) have the same data structure. I want to extract all the data from the source files and merge the data to a single (master) file. See the image below.

The name of the source files can be anything. However, please make sure that all the files (except the master file) are in same folder.
The Master file, which will extract and merge data from all the files, should be in another folder. You can modify it further according to your requirement.

👉Now, if you want to merge multiple Excel files into a single file but different sheets, you must read this post.

You need to write the Macro (or the code) in the Master file, as it will extract data from other files and write the data in it.

I have a Button on my master file. The button is an ActiveX control. The click event calls a functions to read all the files in a folder.

In the above example, I am using VBA FSO (or File System Object) model for accessing files in a folder. The FSO provides object-based tools for working with files and folders. This is one of the easiest ways to get to know everything about the files in a folder.

I am creating an object of FSO.

Set objFs = CreateObject('Scripting.FileSystemObject')

Next, an object to read the folders

Set objFolder = objFs.GetFolder('D:somefoldersample')

👉You can read more about FileSystemObject here.

As I have said before, the name of the files can be anything. However, the structure of each files must be same.

I’ll loop through each files in the folder. Using the File path, I am opening each workbook (the Excel file) one by one to read the data from it.

Set src = Workbooks.Open(file.Path, True, True)

Excel Combine Data From Multiple Sheets

Now, with the source file opened, I’ll get the total rows and columns from the file. Look at the UsedRange property. I don’t want to loop unnecessary through all the rows and columns.

Script to combine excel files

Combine Multiple Excel Files Into One Sheet Vba Example

That’s it. Now read each cell’s value in the source and write it to the Master file.

Thanks for reading.

← PreviousNext →