How To Combine Multiple Workbook to One Workbook In Excel

How To Combine Multiple Workbook to One Workbook In Excel


hi everyone welcome to excel ten
tutorial in this advanced tutorial I will show you how to combine multiple
workbook into one workbook in Excel this is an advanced tutorial and I will use
Excel VBA for this tutorial remember I am using Microsoft Excel 2010 for this
demonstration let’s get started this is the master workbook I want other workbooks to
be combined and this is the folder where I have three excel workbook I want these three to be
combined in this workbook here this is my first workbook let’s open it see I
have ten thousand data here and this is the second one and this is the third one
and I want to combine all this three into this one master book okay so I’m going
to use VBA code to do that let’s go to the Developer tab click here if you
don’t have Developer tab in your Excel i have a tutorial on this one so you can
check that out okay so you click on the Developer tab click on visual basic
click insert and click module now I’m going to need a code okay
and I have already written one you will find this code in the comment section
and I will pin this code for you okay make sure you copy it from there
so here is my code and simply copy and paste it here you see here this path
this is empty and I’m going to add the path of the three workbook I want to
combine so to get the path open the folder let me show you this is the
folder then this is the tree workbooks I want to combine and I need the path of
this folder which is I will get from here
click here and see this is the file path of this three folder copied and paste it
here see now I have the file path this is the path and end it with the
that’s it I have the full path of the three workbook I want to combine okay now
simply click this button run button and close it so this is our master workbook
and check this out this is one workbook and this is another and this is another
delete is empty Sheet so
this was the first sheet and I have combined it to the market of Abu this is
one workbook this is another and this is the third one see I have successfully
combined all three workbooks into one thanks for watching if this
video was helpful give it a thumbs up if you need any help with Microsoft Excel
you can ask me in the channel discussion section share this video with your
friends and please do subscribe it means everything to me this is Kazi signing
out from Excel 10 tutorial thank you

74 thoughts on “How To Combine Multiple Workbook to One Workbook In Excel

  1. Did you subscribe? Please DO.
    Signup here to get the code: https://forms.gle/H27wWWztmm6ezQZv6
    If you can please SUPPORT the channel: https://www.patreon.com/excel10tutorial

  2. Nice video its helpful… But can you advice what code do we need if we need to combine all data in one sheet.All 3 workbooks data in one sheet. Thanks

  3. Hi, thank you for the useful video. i had try the code and follow the step as you show. when i click/ select the run button it appear a message from Microsoft Visual Basic "Run-time error '52': Bad file name or number " below have END, DEBUG AND HELP button. Could you guide me?

  4. Thanks for the video. When i click/ select the run button it appear a message from Microsoft Visual Basic
    Run-time error 1004, with highlited:
    Sheet.Copy After:=ThisWorkbook.Sheets(1) – stating that the copy method of Worksheet failed

  5. Nice Video. I have 3 Workbooks all are having 3 Different Sheets and Sheet Names. I want to Merge First Sheet of Each Workbook. Can you tell me how?

  6. Could you help me to resolve the error?
    I am trying with same code but getting error 1004- copy method of worksheet class failed.
    Here is the code-
    Sub Getsheets()
    'Update file

    Path = "C:UsersHG0027003DesktopKPI Report DataTest"

    Filename = Dir(Path & "*.xls")
    Do While Filename <> ""
    Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
    For Each Sheet In ActiveWorkbook.Sheets
    Sheet.Copy After:=ThisWorkbook.Sheets(1)

    Next Sheet
    Workbooks(Filename).Close
    Filename = Dir()
    Loop

    End Sub

  7. This code is only if the user have addressed the path into the module. If user want to select another folder, they must change the address in the module again and again..
    Would be better if user can select every folder which they want to open without re-set the address on the module. I mean browse for folder using dialog box its more useful.

  8. Hi, what if for example I have 3 tabs for each excel files and I only want one tab to be copied to the master file? How to do it? By the way I am using excel 2016 version. Thanks in advance!

  9. I don't mean it in a bad way, but it would have been nice if you would have put the code down below. We are not IT specialist, and very easily a mistake can be typed in the code, and nothing will be working. It is also very hard to read the code 🙂 Can you provide that please, and perhaps email it to me too at [email protected] ? Thanks so much 🙂

  10. Sub GetSheets()
    Path = "C:UsersdtDesktopdt kte"
    Filename = Dir(Path & "*.xls")
    Do While Filename <> ""
    Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
    For Each Sheet In ActiveWorkbook.Sheets
    Sheet.Copy After:=ThisWorkbook.Sheets(1)
    Next Sheet
    Workbooks(Filename).Close
    Filename = Dir()
    Loop
    End Sub

  11. Great job. I subscribed your channel. I tried to made this macro and also data copied But at last I get error "1004". Please advise on this. And 2nd if all required files and also Macro file be in the same single folder then it has some problem, because macro tried to open own file also. Please advise how to avoid this ?

  12. I have 1 work book,
    That contains A, B & C data, needs to make 3 different work book that's contain A data , B data and C data, pl help

  13. hi , i have three files with multiple sheets but i want a particular sheet from all the three files. say three files names are product 1 and product 2 and product 3 and these files contains many sheets but i need only sales sheet from each file

  14. good KISS presentation. One minor error at time point 2:47 you say "forward slash" but you actually (and correctly) add a backslash ie ""

  15. Do you know how can I consolidate specific tabs from different worksheets into one worksheet? What I mean is that the code should just select some specific tabs form multiple worksheets in order to create one

  16. I am using excel 2016, the code is not working for me. Do you think do I need to make any changes within the code that it can work with excel 2016 or am I missing anything else?

  17. Thanks Man. It helped 🙂

    Would appreciate if some u can help a little more

    What if the files to be combined are either .xlsx or .xls ?

  18. Brilliant!! Love this. I have one tip and one query. My query is that I have workbooks with numbered sheets 2.1, 2.2, 2.3 etc. but unfortunately it puts them in reverse order of the table they come from so 2.3, 2.2, 2.1 etc. – is there anywhere in the code that I could change that?  Now, the tip – it wouldn't run at all at first and didn't look like yours. Then I realized I'd been sent .xls files rather than .xlsx and as soon as I added the x it worked – very exciting as I had over 100 worksheets to combine into one.  Thanks soooo much.

  19. I have 3 separated sheets with SAME headers (but different data). They have only one tab (one.xlsx + to.xlsx + three.xlsx). I'd like to merge them into only one sheet (MASTER.xlsx)… copying them into the FIRST tab… BUT with only one header and below the three sheet's raw data.

  20. Sub GetSheets()
    Path = "C:UsersHPDesktopBAY 2 DAILY UPDATE"
    Filename = Dir(Path & "*.xls")
    Do While Filename <> ""
    Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
    For Each Sheet In ActiveWorkbook.Sheets
    Sheet.Copy After:=ThisWorkbook.Sheets(1)
    Next Sheet
    Workbooks(Filename).Close
    Filename = Dir()
    Loop
    End Sub

    so how would I get this to run again on daily bases updateing the master workbook automatically

  21. I just tried but no changes happened when I run the code. Is it because I have over ten spreadsheets. How can I connect with you

  22. Hi, I've tried this several times and I can't get it to work. Follow all instructions to the letter. Please advise. Thanks

  23. Please help how can I merge specific sheet name. Suppose I have got 3 workbooks each with three sheets namely a, b, c: within each workbook I just wanna merge only a sheet namely "b", what can i do?

  24. Thank you so much for the share, however i have 2 problem. the file to be combined are opened and asked to be saved. and each file have 2 sheets and i want to combine first sheet of all workbook only. Appreciate if you can give me solution on this 2 problem. thank you in advance and great work.

  25. I'm looking for the code to copy paste. The three workbooks I have have 20 sheets each all the same and I'd like to combine them into one workbook.

  26. The video is helpful. However, I am having a problem to run the macro with the coding as per your video shared. The compile error said variable not defined. Appreciate for your advice. Thanks

  27. Hi, thanks for the video.

    I want a solution to combine selected sheets of many workbooks in 1 workbook.

    for example i have 2 files:

    File 1 – Part1.xlsx (Sheets – Part1_Data,Chart)
    File 2 – Part2.xlsx (Sheets – Part2_Data,Chart)

    I want to put Part1.Data and Part2.Data sheets in 1 new Excel file (Don't want to copy in workbook having code, it should be separate).

    Can it be done? Please share the code

  28. Hi I want to pick only particular sheet from multiple workbook to master ,like I have 6 workbook which has 3 sheets in each workbook ,and I want to pick only Jan month sheet from all work books out of 3 sheets in all workbooks

  29. May you please help me with the script where i dont have to open the spread sheets because i have about 100 spreadsheets that i need to extract data from .

  30. This is an excellent work. But I have a problem. filename says that is empty. I have excell2016 if this matters. I tried several things but still the same problem. could you help me please?

Leave a Reply

Your email address will not be published. Required fields are marked *