• Home  / 
  • VBA
  •  /  Merge Excel Files into a Spreadsheet using Excel Macro

Merge Excel Files into a Spreadsheet using Excel Macro

In this blog, we will try to cover how to merge excel files into a single sheet from a different workbook using excel macro. In the earlier blogs, we have covered how to merge data in a single sheet when the data are coming from the same sheet. If you don’t follow our blog, then please click here to read our earlier article on Merging Multiple Excel Sheets into a Single Excel Spreadsheet using VBA

In this blog, we are trying to cover technique to merge excel files in a single sheet when the data is saved in separate files in a location. Now think if you open the file one by one and manually combine excel files, how long time will it take?

At the same time, a small piece of macro code will do the trick to merge excel files for you even though you are not a programmer. All you need to know is how to paste the VBA code and run it.

Here’s a Step by Step process to merge excel files using excel macro code:

First of all, Save the files in one Drive/Folder and link to all practice files used in our example D:\Collate Multiple Files. It’s necessary to keep all the files in one location to merge excel workbooks into single spreadsheet

It is necessary to keep all th files in one location to merge excel workbooks into single sheet - Excel Macro

There are lots of files. We need to merge the same into a single spreadsheet. Make sure the format or the heading of all the files are the same.

Suggested: Learn Macro coding with Advanced Excel VBA Tutorial

I have created a dummy book called Book1 in the same place. I will merge all the data in this book.

Follow the process mentioned below to do the same.

1. Open the workbook called Book1.
2. Press Alt + F11 to the VBA page
3. Click on Insert and then Module
4. Copy and paste the below macro code

After pasting the code, please press F5 to run the excel macro code. It will open a file and then copy the data and paste the same in Book1 and close the workbook that has opened.

Now please check the same whether the code is working perfectly or not. First of all, have a look at the Book1 file. It looks like below and there is no data resides in the book except the header.

Opened book1 and run the code is working perfectly or not first a look at the book1 it looks like there is no data resides in the book except the header - Excel Macro
Now press Alt + F11 and paste the below excel macro code to merge excel files and it will look like below.
Run the Excel macro code

Then you need to press F5 to run the excel macro code. It will then open the file one by one and paste the data in Book1. After running the loop, it will give you a message that it has successfully copied and pasted the data in Book1.
It will you give you a message that it has successfully copied pasted the data in Book1 -Excel Macro

Please click here to download all the Excel Macro code files for practice. For your convenience, I have also shared the different books so that you can practice.

Try to Free excel macro tutorial, Get a head start with macro codes

I hope you found our article useful. If you any doubts or questions regarding this article, feel free to post them in the comment section below.

About the author

Niladri Sekhar Biswas

Hi, I have 9 years of experience working in Microsoft Excel. I am also an Excel and VBA corporate trainer. Writing about Excel and sharing my knowledge is my passion. I hope you learn more about Excel from my articles.