It’s been a while since my old post about merging multiple excel files, but still i received so many question regarding excel merger with MS Office 2007 vba macros for various conditions or situations. Therefore i beg your apologies for all un-replied emails, comments and messages that i received via Youtube, Gplus, Facebook and also twitter mention. It’s all because of this daily offline activity that i had which keep my fingers away from maintaining this blog.
Anyway, in order to reply questions regarding excel merger macros script (at least some of them), today i’m going to share 2 simple tweaks to customize script from the old post of mine, which will allow you to merge worksheets from multiple workbooks (excel files) into worksheets on a single file, let’s name it worksheets to worksheets merger. The other one will let you merge multiple worksheets within workbooks to a simple worksheet within a single workbook, let’s name it worksheets to worksheet excel merger. Please keep in mind that there are limitation for maximum number of rows on a single workbook.
The following macros script is based on macros script post described on above first paragraph, please take a notice on commented lines below to see the different between source and current tweaked vba macros script.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
Sub simpleXlsMerger() Dim bookList As Workbook 'Add 2 new variables for sheets count Dim a, SC As Integer Dim mergeObj, dirObj, filesObj, everyObj As Object Application.ScreenUpdating = False Set mergeObj = CreateObject("Scripting.FileSystemObject") Set dirObj = mergeObj.Getfolder("C:\Users\Asus\Downloads\Excell\Excell\") Set filesObj = dirObj.Files For Each everyObj In filesObj Set bookList = Workbooks.Open(everyObj) 'Counting sheets and iterate SC = Sheets.Count For a = 1 To SC 'set active sheet trhough iteration and copy bookList.Worksheets(a).Activate Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy 'move to target workbook, related sheet and paste ThisWorkbook.Worksheets(a).Activate Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Application.CutCopyMode = False 'Next sheet Next a bookList.Close Next End Sub
Well, not really a tweak thought, but those line really give a different result. All sheets from source workbooks will be copied into targeted workbook.
Using the same above macros, change target to single worksheets by changing the following line
1 2 3 4 5 6
.. 'move to target workbook, select first sheet and paste ThisWorkbook.Worksheets(1).Activate Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Application.CutCopyMode = False ..
Change Worksheets(a) to Worksheets(1), it will then copy all worksheets within workbooks into to first worksheet of targeted workbook. That’s it for now, hope to write for more answer regarding excel merger macros next time. I also made a tiny useless excel merger tool, slow and work only for single worksheet but surely work for fine me which i hope work well to you also.