I just started learning VBA script writing and Makros. I managed to write a script whereby I merge multiple excel workbooks into one master worksheet. At this point, I have accomplished one part of the task. However, I couldn't proceed to make the master file to auto-update automatically if I add or change a data to the slave files. Below is the code that I have written.
Sub MergeExcelFIle() Dim wb As Workbook Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object Application.ScreenUpdating = True Set mergeObj = CreateObject("Scripting.FileSystemObject") 'Include your path folder here containing all excel files 'Set all objects relevant to the folder and list down all the files needed for the merging Set dirObj = mergeObj.GetFolder("C:\Users\pathhhhh") Set filesObj = dirObj.Files For Each everyObj In filesObj Set wb = Workbooks.Open(everyObj) 'Change cell reference to the starting point of your data 'For example, if you begin with cell A2 to merge, start your range from A2 'Change ''A" column on "A65536" to the same column as start point Range("A2:AZ" & Range("A65536").End(xlUp).Row).Copy ThisWorkbook.Worksheets(1).Activate 'Do not change the following column. It's not the same as above. Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Application.CutCopyMode = False wb.Close Next End Sub
My code copies multiple excel files into a single master worksheet. I have changed the code to ensure that the code captures data to be copied within a selected range.
Is there any functions that I need to key-in within the code to enable the master file to retrieve data automatically if there are changes made to the slaves file? I have tried PowerQuery as a substitute for this but I don't think that PowerQuery has the function to enable the master file to maintain the same layout as what I want and to input photos as well within the file itself (Correct me if I am wrong).
I am also open to suggestions if there's anything wrong with my code. Let me know if there's anything I can do to improve myself. Thanks everyone!