Need to Merge Data of Multiples Excel Files Together Using VBA
Hi everyone!
I need to create an Excel macro using VBA which can merge more than one excel files keeping headers, columns and sheets.
Two files are attached as examples which need to be merged into one. Format of both files is same.
Every week I get such new file so I need a Macro to add data of the file to previous ones and hence final file at time of running macro should include data of all files merged together.
I have been able to create following code but unable to proceed further successfully.
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("D:\change\to\excel\files\path\here") 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
Any help or guidance will be appreciated in this regard !