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.