Marco to auto update

[U412178]

I'm looking for help to create a marco that will auto update a workbook, "Master.xlsx" upon opening from another workbook, "Test.xlsx". Is there VB code that will automatically update when opening "Master"? The thing is that "Test" workbook will have rows added in between the existing rows, so the marco needs to update every single row and not just the last few rows of data. Hopefully this makes sense.

And I really appreciate your help with this!! Thank you!!

 

[ColinKJ]

Hi U412178,

I assume you are taking data from Test WB into Master WB, and you want this to happen automatically when you open the Master WB.

Is it possible for you to post an example of how you see your Master WB before update, and an example of the data in the Test WB, and an example of how you see the Master WB after the update.

 

[U412178]

Yes, the Test WB updates from the Master WB. What happens is the Test WB gets updated from a system that I have at my business and nothing I do in that workbook can be saved. But since I need to make changes to that data I need to be able to save the data. So I wanted to run an automatic marco that would pull the data from the Test WB into the Master WB. Does that make sense?

 

[ColinKJ]

Hi U412178,

Sorry about all the questions, but I would need to know a bit more about the data / Master.

I assume that there is a reason you can't just copy the data and paste it into your master WB.

Is the data in the Test WB in the same arrangement as that in the Master, do you want the data from the Test to be added to the Master or to replace any data already in the Master.

 

[U412178]

Yes, the data in the Test WB is in the same arrangement as want I want it to be in the Master WB. The problem is that the Test WB will changing every week so I would rather not copy and paste the data. I would rather a marco pull the Test WB data into the Master WB, so the new weekly data in the Test WB would replace the data from "last week" in the Master WB.

Hopefully that makes more sense now...Thank you so much for your help!!!!

 

[ColinKJ]

Hi U412178,

Without more specific information about the layout / format of your WBs / Data, both of the following will copy data from The Test WB to the Master WB.

The first, assumes both WBs are open, and that Sheet(1) is the active sheet in both WBs, and the code is in the Master WB.

Sub CopyToMaster()
Workbooks("Test.xlsx").Activate
Sheets(1).Cells.Select
Selection.Copy
ThisWorkbook.Activate
Sheets(1).Cells(1, 1).Select
ActiveSheet.Paste
End Sub

The second assumes the code is in the Master WB, and the Test WB is not open, but is saved in the same folder where the Master WB has been opened from.

Sub CopyToMaster1()
a = ThisWorkbook.Path
Workbooks.Open a & "Test.xlsx"
Sheets(1).Cells.Select
Selection.Copy
ThisWorkbook.Activate
Sheets(1).Cells(1, 1).Select
ActiveSheet.Paste
Workbooks("Test.xlsx").Close savechanges:=False
End Sub

I've tested both here, so they should work for you.

It is best if you first test these by creating a Master WB & a Test WB with some dummy data in a test folder.