Merging multiple workbooks into a single master workbook

See more:

Office

MS-Excel

VBA

I am working to merge multiple workbooks first sheet into a master workbook sheet using MS Office 2013 VBA code. Everything is working fine except when copying data from source sheets it only copies a portion of it (for example sheet A has 190 columns and sheet B has 250 columns and sheet C so on... but it is only importing like 16 columns from Sheet A and 10 from Sheet B but not all of them), here is the code I have written for it:


Any help will be much appreciated :)

What I have tried:

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\hnoorzai\Desktop\Newfolder")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
    Set bookList = Workbooks.Open(everyObj)
 
    'Change B3:H to the range your working on and also B in B65536 to any column required.
    bookList.Worksheets(1).Range("B3:H" & Range("B65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
 
    'Below only change "B" column name to your required column name
    Range("B65536").End(xlUp).Offset(1, 0).PasteSpecial
    Application.CutCopyMode = False
    bookList.Close
Next
End Sub