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

mrbuttons · Dec 18th 2019

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.

Code

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.

Code

'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!

**royUK** · Dec 18th 2019

You need to restore ScreenUpdating when the code has finished. ScreenUpdating will not switch itself back on.

davesexcel · Dec 18th 2019

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.

Code

Sub with_SU()
    For Each cell In Range("A1:K22").Cells
        cell.Select
        cell.Value = cell.Row & " _ " & cell.Column
    Next cell
End Sub
Sub with_SU2()
    Application.ScreenUpdating = False
    For Each cell In Range("A1:K22").Cells
        cell.Select
        cell.Value = cell.Row & " _ " & cell.Column
    Next cell
End Sub

Display More

**royUK** · Dec 18th 2019

No it doesn't, that's a misconception. I have seen the problems caused, especially when testing other people's code.

Read this

davesexcel · Dec 18th 2019

Ah, good read. Guess I have never run into a problem so assumed it was a hoax to set it to true.

I started with xl2000 never had to then, won't be difficult to get into the habit now.

**royUK** · Dec 18th 2019

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.

mrbuttons · Dec 19th 2019

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!

**KjBox** · Dec 19th 2019

That was an interesting article, Roy.

However, if you run this

Code

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)

**KjBox** · Dec 19th 2019

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.

davesexcel · Dec 19th 2019

re Screen updating.

I do believe screen updating will pass through to another code when called, but once you are back on the worksheet, screen updating is set back to true.

mrbuttons · Dec 24th 2019

I see. I guess it doesn't revolve around screen updating that much.

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

Participate now!

Share

Tags