Merge Multiple Files into One Worksheet

[mandeep911]

Hello,

I am trying to merge multiple files into one workbook. The files are saved in one folder but have multiple sheet in them. I would like to copy data from only one sheet (""DAILY_DETAIL") and combine to one sheet.

I am trying to use the below macro but for some reason it is copying A1:AS to A2:AS. Not sure why this is happening I was able to use this code with files that had only one sheet.

Any Idea?


Sub simpleXlsMerger()
Dim bookList As Workbook
Dim bookSheet As Worksheet
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Application.EnableEvents = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")

Set dirObj = mergeObj.Getfolder("N:\AOC\Access and Support\Access\Reports\2016")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)

Set bookSheet = bookList.Sheets("DAILY_DETAIL")
bookSheet.Range("A2:AS" & Range("A1048576").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate

Range("A1048576").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
Application.ScreenUpdating = True
Application.EnableEvents = False
End Sub