Merge multiple excel books into a single master worksheet. Master sheet to auto update automatically if data is changed in slave books.

  • 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.

    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

    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!

  • royUK

    Screen updating does turn back on after it exits the sub

    Test it out, with and without screen updating. screen updating not set to true.:thumbup:

  • I've always reset it, I also tend to put an error handler in to make sure it resets.

    You get a really good effect if you drag a userform with screenupdating off.

  • Hello guys.

    Not sure about the screen updating function here. Do I need to set it as true to enable the data to be updated automatically? Thank you!

  • That was an interesting article, Roy.

    However, if you run this

    Sub test()
        MsgBox "ScreenUpdating is " & Application.ScreenUpdating
    End Sub

    after running the code in the article, you will find that ScreenUpdating did, in fact, turn itself back on when Sub One completed (after calling Sub Two and Sub Three)

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • mrbuttons, your code sets ScreenUpdating to True at the start of your code (Line 4). Nowhere in the code does it get turned off, so there is nothing for you to do concerning ScreenUpdating.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!