Customize excel merger macros script, copying worksheets

Last updated on February 27, 2015 @ No comments yet

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.

Worksheets to worksheets merger (copy to related sheets)

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.

excel merger vba macros script

Worksheets to worksheet merger (copy all to single sheet)

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.

M Riza

Article by M Riza

Is a blogger since 2007, founder and editor of Oa Ultimate. Working as a network administrator, computer technician, database and web maintainer | Twitter | G+

Leave a Reply

*

Loading Google+ Comments ...
Loading Facebook Comments ...