How to merge 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 technique 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?
Steps for Merge excel files in Excel using excel macro:
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
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 in 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
Sub MergeDataFromWorkbooks() 'DECLARE AND SET VARIABLES Dim wbk As Workbook Dim wbk1 As Workbook Set wbk1 = ThisWorkbook Dim Filename As String Dim Path As String Path = "D:\Collate Multiple Files\" 'CHANGE PATH Filename = Dir(Path & "*.xlsx") '-------------------------------------------- 'OPEN EXCEL FILES Do While Len(Filename) > 0 'IF NEXT FILE EXISTS THEN Set wbk = Workbooks.Open(Path & Filename) wbk.Activate Range("A2").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Windows("Book1.xlsm").Activate Application.DisplayAlerts = False Dim lr As Double lr = wbk1.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row Sheets("Sheet1").Select Cells(lr + 1, 1).Select ActiveSheet.Paste wbk.Close True Filename = Dir Loop MsgBox "All the files are copied and pasted in Book1." End Sub
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.
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.
Press Alt + F11 and paste excel macro code
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.
Also, You can Download Excel Macro Code Files for 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.