How to COMBINE Multiple Excel WORKBOOKS into One Workbook

How to COMBINE Multiple Excel WORKBOOKS into One Workbook

Combine multiple excel workbooks into one workbook

We might need to combine multiple excel workbooks into one workbook in order to analyse or present the information better. Though this can be done manually, by moving or copying all the worksheets from different workbooks into one workbook, but it would be time consuming and prone to errors. Moreover if there are a large number of workbooks/worksheets, then the problem is even bigger.

However, we have a solution to this and it would not take more than a few seconds to get all workbooks merged into one. Here is the VBA code that can help us achieve this. Even those who are not well versed with VBA can easily get this done by simply following the steps on how to use this code:

VBA Code for XLS Files

FREE DOWNLOAD

Send download link to:

VBA Code for XLSX Files

FREE DOWNLOAD

Send download link to:

How to Use this Code?

Below are the steps to use this code and there’s also a video at the end:

  • (1) All the Excel workbooks that you want to combine should be placed in a folder – For the purpose of this tutorial, we have created a folder named “Excel Workbooks” and have three files in it.

Workbooks to be combined

  • (2) Open a new Excel workbook.

Open a New Excel Workbook

  • (3) Press ALT + F11 (or go to Developer –> Visual Basic). This will open the Visual Basic Editor.

Visual Basic Editor

  • (4) In the VB Editor menu go to Insert –> Module. This will insert a module for the workbook with a blank code window.

Insert Module in VBE

  • (5) Copy and paste the above code into the code window.

Copy the VBA Code in the Code Window

  • (6) In the code, you need to change the following line of code:
          Path = ""

  • (7) In this line, insert the location of the folder in which you have placed the files that you want to combine, between quotes. In the code used above, the folder is in “Documents”. Get the path by visiting the folder and clicking on the address bar dropdown. We insert C:\Users\Nitesh\Documents\Excel Workbooks\ between the quotes. (Note that a back slash has been added at the end of the folder path)

File Path

  • (8) Place the cursor anywhere in the code and click on the green play button in the Toolbar options (or press the F5 key).

Play Button F5 Key

  • (9) This will activate the code and all worksheets from all the Excel workbooks in the folder would get consolidated into a single workbook.

Combine Multiple Excel Workbooks into One

  • (10) Save the Master Workbook, and you’re done.

Save Workbook

How does this Code Work?

The code uses the DIR function to get the file names from the specified folder.

The following line assigns the first excel file name to the variable ‘Filename’.

Filename = Dir(Path & “*.xls*”)

Then the Do While loop is used to check whether all the files have been covered.

Within the Do While loop, For Each loop is used to copy all the worksheets to the workbook in which we are running the code.

At the end of the Do Loop, following line of code is used: Filename = Dir(). It assigns the next Excel file name to the Filename variable and the loop starts again.

When all the files are covered, DIR function returns an empty string, which is when the loop ends.

Here is an explanation of the DIR function in the MSDN library:

Dir returns the first file name that matches pathname. To get any additional file names that match pathname, call Dir again with no arguments. When no more file names match, Dir returns a zero-length string (“”).

We hope you found this interesting. Your comments/feedback would be appreciated.

Video

 
Comments

tried doing the above but it gives a window prompt for the “Macros Name”

Vasudha, please follow the instructions carefully. Try it again and let us know if the issue persists.

Sardar Hussain

Dear Sir

unable to get the code

Hi Mr. Hussain.

The code is clearly provided in the post above. Even the usage instructions are provided in the post as well as in the video. Kindly let us know if its still an issue.

The code is not there, maybe you have a cached page, or maybe it is being blocked in some way, could you please post the code here? Please!

Kindly download the code from the above link. Thanks.

Thank you so much. I have never used the VBA code before but I knew it was there and I knew someone must know how to pull multiple files into one! I followed your directions and like magic it worked. Now to learn how to do more than ready directions and copy/paste. 🙂

Glad you found it useful. Stay connected, will come out with more value added stuff going forward.

Hi Sir, I can’t find where to copy the code, and I am just typing it now but I’m afraid I’m going to messed up with my sheets coz I didn’t copy the code well. Is there any way to paste those code here? tHanks!

Please refer to the video, you’ll get exactly where to paste the code.

tried doing the above but it gives a window prompt for the “Macros Name”

Please follow the steps carefully. Also refer to the video. It will work.

Good day, Please tell me if the VBA code can also work for CSV files?

Thanks

No, it will work only on XLS and XLSX files.

Abena Berko Sakyi

will you use this specific code for all merges that needs to be done in excel. assuming i have different project will this code work for all of them?

No, this code is specifically designed for merging several workbooks into one workbook. Depending on the requirement the code will vary. For instance – a similar code which involved merging only ONE sheet each from several workbooks would need a different code explained here > https://www.exceljunction.com/how-to-merge-first-worksheet-of-multiple-excel-workbooks/

Hope that answers !!

Abena Berko Sakyi

how can i copy and paste the vba code?

I don’t have the developer/visual basic menu option and nothing happens pressing alt and f11. I have excel for office and student build 1903. Why wont it open vba?

To get the Developer Tab in the Ribbon, do the following:

Click File
Click Options
Click Customize Ribbon
Under the list of Main Tabs, select Developer
Click OK

Once done, press Alt+F11

Leave a Reply

You must be logged in to post a comment.