Merge Workbooks in One Workbook using VBA - Macro

Day in Day out in our business we deal with consolidating or merging multiple excel workbook and so it becomes tedious task to merge files in bulk, why we cannot prefer an automated process to perform this task and ease our life. The simple method works very effectively as I m practicing in my work daily. I have prepared a simple and few lines of VBA script. And I just run it whenever I required and make use of this consolidated data to generate multiple reports or summary of business...

I have two different approach of getting the data from multiple workbook and use it differently. 

1. Consolidate the workbooks data one below the other for data which is in same format

2. Consolidate each workbooks data in different sheets (tabs) where data is irrelevant and not in same format or unstructured.

Using "Scripting.FileSystemObject" object I dynamically access the folder to look for source files.

Let us see the script for consolidating the data which is in same format...

Obviously the first part of the code would be declaring the variables explicitly.

As shown in above image, I've declared the few variables that will create Scripting FileSystemObjects for me and the same I can use to access the source files.

Creating Objects dynamically, I've create an object objFile as "Scripting.FileSystemObject" , with help of this object I can access a particular folder which I mentioned in next line of procedure.

You can even directly declare object variable in your procedure like

      Dim objFile As Scripting.FileSystemObject

to declare the object you need to access the GUID references in VBA Project

Click Tools on VB Editor ribbon->Click References from drop-down

Look for "Microsoft Scripting Runtime" from the list 

Tick the Check box next to it (as shown in below image)

 

Now Loop through the folders (Learn more on LOOPS in Excel)

To access each workbook in folder and determine the range by finding lastrow and lastcol to copy. Paste the sane in activeworkbook.

Finally Reset the application properties

With this vba code you can consolidate or merge data from each accessed workbook and paste the same one by one below in same sheet.

Now Let us write a code to copy each workbook data from sheet 1 and paste in thisworkbook in different sheets one by one. This code will help to consolidate the data which is unstructured or of different format.

As you know we have to declare variables explicitly

Now in Loop there is sort of change

As we are copying unstructured data from workbook and pasting in different sheets, so we will simply paste the copied data in range "A1" of each sheet. and also here I have written the line to name each sheets tabs with file name.

            Sheets.Add After:=ActiveSheet
            ActiveSheet.Name = VBA.Left(objEachfile.Name, _
                                (Application.WorksheetFunction.Find(".", _
                                        objEachfile.Name, 1) - 1))
                                                   
            Cells(1, 1).PasteSpecial
 
This piece of code adds new sheet after each activesheet and assigns the name as filename.

To extract filename I've used LEFT Function and Find Worksheet function as shown below.
VBA.Left(objEachfile.Name,(Application.WorksheetFunction.Find(".", objEachfile.Name,1)-1))

Lastly you must trap the error that may occur at the time of code executions.

Errors like, file not found, No Data available and so on

Always follow Coding Standards in your every projects  

Hope this helps you to understand the method of automating the consolidation of multiple workbooks in active workbook.

Download the Practice Workbook