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.
Excel VBA Macros for Non-Coders
Do you find VBA Macros scary? Did you miss any job opportunity because of it? Get started today with our eBook guide on using – Excel VBA Macros. 140 pages of rich visuals. Download now.
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 a 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 is 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
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”).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 also, 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.
Excel VBA Macros for Non-Coders
Do you find VBA Macros scary? Did you miss any job opportunity because of it? Get started today with our eBook guide on using – Excel VBA Macros. 140 pages of rich visuals. Download now.
thank you… it is a very nice macro and works well.
Hi
Can i get help on combining workbooks, its bit complicated.
Good Post
Not working.
Its working sir, Please follow the step properly. if you still facing issue. You can post your question on https://www.facebook.com/groups/yodalearning.tribe/
We have our expert who is running this group, they will help you with your question.
Thank You.
Its working, Please recheck the VBA Code
its not working did everything as explained.
Didn’t work.
where is Book1?? In this combine sheets using excel vba ?
Its really helped my alot thanks for the tutorial Sir
Hey Robertwright,
Sorry for the inconvenience, but if you are still having issue with the code. Drop us an email on [email protected] with your excel file and we will fix the issue and send back to you.
Hope you are fine with this
Regards,
Darshan
It’s working fine, just make the file directory properly and you will get it.
Stil find any issue, check with the sample files
I have workbooks with 3 sheets in it. I would like to have a combined workbook with the data from workbook1.sheet1 +workbook2.sheet1 in to my master file sheet
same way workbook1.sheet2+workbook2.sheet2 in to my master file sheet2
pl give the vba code
Hi i am not getting the merged data but a pop up saying all files are copied and saved is coming directly may i know why was not i am getting the data .
Check the extension of the file or check if the file is in binary excel or not
Excel Macro is helping me merge in other spreadsheet. I want to know how to merge with Excel VBA.
thanks Yodalearning.
The files are copying … but only the first 2 columns of a 59 column spreadsheet are coming through. All rows are coming through. Here is my VBA:
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 = “C:\Users\100tdavis\OneDrive – Make-A-Wish America\Walk for Wishes\009 Colorado\Test Concatenation\” ‘CHANGE PATH
Filename = Dir(Path & “*.csv”)
‘——————————————–
‘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”).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 Book 1.”
End Sub
Any suggestions??
Thank you!
Hi, Getting Compile error while giving Path Location. Please help
Please check for the extension is it xlsm or xlsx
It works 100%. Thanks! Cheers
its total a wrong program
may be before posting you should check the code..
Can you please check again. This code is working 100% please check each step to discovered the issue you are facing.
Regards,
Yodalearning Team
Can you please share the formula which can be copied, as am not able to download the same.
Here’s the 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”).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