Hi all,
I'm trying to create a master file which needs to be read only in Excel format. The information for the master file is integrated from seven different excel workbooks and only a particular information (eg. Column B and Column E only) is outsourced for the master file. When the information in the workbook is updated, the master file needs to show the most updated status. I've created a vba code which it can copy and paste data from workbooks to master workbook only when each time I click the play button in the xlsm file. It doesn't update the master workbook automatically when the user modify data from those seven different excel workbooks. Is there any way that I can make it update data automatically to master workbook each time when user modified the content? Also, do I have to create the vba code in master workbook or do I need to write the code in separate file? Because in my case, I wrote my workbook in separate workbook.
Below is my code:
I'm trying to create a master file which needs to be read only in Excel format. The information for the master file is integrated from seven different excel workbooks and only a particular information (eg. Column B and Column E only) is outsourced for the master file. When the information in the workbook is updated, the master file needs to show the most updated status. I've created a vba code which it can copy and paste data from workbooks to master workbook only when each time I click the play button in the xlsm file. It doesn't update the master workbook automatically when the user modify data from those seven different excel workbooks. Is there any way that I can make it update data automatically to master workbook each time when user modified the content? Also, do I have to create the vba code in master workbook or do I need to write the code in separate file? Because in my case, I wrote my workbook in separate workbook.
Below is my code:
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub
Sub AutoSave()
Dim a As Workbook
Dim b As Workbook
Dim x As Workbook
Dim y As Workbook
'## Open workbooks first:
Set a = Workbooks.Open("C:\Users\pwloo\Desktop\mm\second.xlsx")
Set b = Workbooks.Open("C:\Users\pwloo\Desktop\mm\third.xlsx")
Set x = Workbooks.Open("C:\Users\pwloo\Desktop\mm\initial.xlsx")
Set y = Workbooks.Open("C:\Users\pwloo\Desktop\mm\master.xlsx")
'Now, copy what you want from and paste to y sheet:
a.Sheets("DS").Range("D:D").Copy Destination:=y.Sheets("Sheet1").Range("E:E")
b.Sheets("PP").Range("C:C").Copy Destination:=y.Sheets("Sheet1").Range("G:G")
b.Sheets("PP").Range("A:A").Copy Destination:=y.Sheets("Sheet1").Range("F:F")
x.Sheets("Sheet1").Range("A:A").Copy Destination:=y.Sheets("Sheet1").Range("A:A")
x.Sheets("Sheet1").Range("C:C").Copy Destination:=y.Sheets("Sheet1").Range("B:B")
a.Sheets("DS").Range("A:A").Copy Destination:=y.Sheets("Sheet1").Range("C:C")
a.Sheets("DS").Range("B:B").Copy Destination:=y.Sheets("Sheet1").Range("D:D")
'Close x:
a.Close
b.Close
x.Close
'Close y:
y.Save
y.Close
Application.CutCopyMode = False
End Sub
Last edited by a moderator:
