0
votes

I have 50 workbooks each having 7 worksheets. Now I need to combine them in such a way that the new workbook should have 7 worksheets and each worksheet should have the respective data from all the 50 workbooks.

For example: Worksheet1 of new workbook must contain the data from all the sheet1 of 50 workbooks. Similarly Worksheet2 should contain all the data from sheet2 of all 50 workbooks and so on... till sheet7.

1
There are many good examples of the pieces you need on this site. Looping through workbooks; looping through worksheets, finding the last row; copying and pasting to a specific row. There are also lots of very similar requests with good answers. Take a look at some of them, modify for your needs and ask specific questions here when you get stuck Happy coding!Doug Glancy
i looked at most of them.... but almost all of them tells how to combine multiple workbook with single sheet into one workbook with multipe sheet. mine is a bit different.user2584171
Specify how does the data from several sheets should be merged into result sheet (row by row, without any gaps?), and where to take source workbooks (all workbooks placed in some folder?). Also add the code you have tried, as @DougGlancy commented.omegastripes
Why not try record macro for first 2 workbooks and look for pattern to create a loop?PatricK
kindly look at my progress and if u can...please correct it.user2584171

1 Answers

0
votes

Sub simpleXlsMerger()

Dim bookList As Workbook

Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object

Application.ScreenUpdating = False

Set mergeObj = CreateObject("Scripting.FileSystemObject")

'change folder path of excel files here

Set dirObj = mergeObj.Getfolder("C:\Users\607182604\desktop\Heinz Jun'15 MI Template\consolidated")

Set filesObj = dirObj.Files

For Each everyObj In filesObj

Set bookList = Workbooks.Open(everyObj)

Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy

ThisWorkbook.Worksheets(1).Activate

Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial

Application.CutCopyMode = False

bookList.Close

Next

End Sub

@dougGlancy and @omegastripes I want the data to be merged Row by Row, Without Any Gaps and yes all workbooks are in the same folders. The problem I am facing is that it is only merging the 1st worksheet of every workbook. What I want is that, it should merge the data of all the 50 excel files with 7 worksheets in one consolidated workbook but 7 different worksheets.