How to merge excel files or combine excel files into a single sheet from a different workbook using excel macro is the topic of this blog. But in the earlier blog, we have covered how to merge data in a single sheet when the data are coming from the same sheet. We are trying to cover techniques to combine 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.
How to Merge excel files in Excel using excel macro:
First of all, Now 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 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.
I have created a dummy book called Book1 in the same place. I will merge all the data in this book.
Steps to combine excel into a single file:
- Open the workbook called Book1.
- Press Alt + F11 to the VBA page
- Click on Insert and then Module
- Copy and paste the below macro code