Hello everyone!
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
Display More
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.
'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
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!