Copy data from one workbook to another workbook regularly (automatically)

[hrhr2]

Good day all,

I have two workbooks: 1) called Master and 2) called Tracking/Update
I would like to copy rows of data from one sheet (called Mail) in workbook "Master" to workbook "Tracking/Update" automatically (without manually doing a copy and paste). Also, new information is added to "Master" everyday so I would like those newly added rows to be copied to the next available row in "Tracking/Update". I have tried to provide an example below:

Data in "Master":
Column A -Column B -Column C
Received date -Name -Instructions
26-Dec-12 -Mark Jacobs -Prepare four letters
26-Dec-12 -Sally Homer -Return to me for review

From "Master" to "Tracking/Update" so that it can be updated:
Column A -Column B -Column C -Column D
Received date -Name -Instructions -Status
26-Dec-12 -Mark Jacobs -Prepare four letters -completed
26-Dec-12 -Sally Homer -Return to me for review -completed

Then the next day when data in "Master" is updated:
Column A -Column B -Column C
Received date -Name -Instructions
26-Dec-12 -Mark Jacobs -Prepare four letters
26-Dec-12 -Sally Homer -Return to me for review
27-Dec-12 -Jessica Fields -Please file

Data copied to "Tracking/Update":
Column A -Column B -Column C -Column D
Received date -Name -Instructions -Status
26-Dec-12 -Mark Jacobs -Prepare four letters -completed
26-Dec-12 -Sally Homer -Return to me for review -completed
27-Dec-12 -Jessica Fields -Please file


I hope that this was clear. Please let me know if you need any further information. Looking forward to hearing from you!​

 

[Iktishaf]

Some clarification, please.
Is Master kept permanently open, or is is opened and then closed as new entries are put into into it?
How soon do you need the new entries copied into Tracking?
How does the data in Column D in Tracking come about? Are they connected with the Copy-Paste operation?

 

[GRTorres]

Hello hrhr2, I am going to go ahead an make some assumptions:

1) The master and tracking file both have a header row for the mail data.
2) Prior day/days data in the master file in columns A, B and C will not change, the only thing that changes is more data on new lines
3) The master file is already updated by the time you want to copy and paste into your tracking/update file

If this is the case, you can place two codes to knock this out pretty easily (I chose to do an Open event to automatically copy and paste your data). This means upon opening your tracking file it will go and copy your data for you right then and there. If you dont want this to happen, skip this code and assign a button to call the second macro below or even run an event timer.

Place below code in your ThisWorkbook vba project sheet

Private Sub Workbook_Open()

Call Sheet1.AutoCopy

End Sub

'Or if you want it to run at a specific time of the day try using:
'Application.OnTime TimeValue("08:30:00") "AutoCopy"

This next one is your copy and paste macro, place in the vba project sheet where data is held.

Sub AutoCopy()

Dim pasteTo As Range
Dim countRows, i As Long

countRows = Application.CountA(Range("A:A"))

Workbooks.Open Filename:="C:\Documents\Master.xls"
i = Application.CountA(ActiveWorkbook.Sheets("Mail").Range("A:A"))

If i = countRows Then Exit Sub

ActiveWorkbook.Sheets("Mail").Range("A" & countRows + 1 & ":C" & i).Select
Selection.Copy
Workbooks("Master.xls").Close
Set pasteTo = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
ThisWorkbook.ActiveSheet.Paste Destination:=pasteTo
Application.CutCopyMode = False

End Sub

Now in the above code, make sure to change tab name for Tracking Update from "Sheet1" as I assumed that was what it was since you didnt supply above. Also change path of file open for Master workbook.
This code worked for me, let me know if you need help with it.

 

[hrhr2]

Some clarification, please.
Is Master kept permanently open, or is is opened and then closed as new entries are put into into it?
How soon do you need the new entries copied into Tracking?
How does the data in Column D in Tracking come about? Are they connected with the Copy-Paste operation?

1) Master is opened and closed as new entries are put into it.
2) New entries can be copied every day or on command (if possible)
3) Column D in Tracking, I hope should be able to be updated manually once the data from Master is copied into Tracking.

 

[hrhr2]

Thank you for your response. Your assumptions are correct, however, I am not getting this to work. I opted to assign a button using the second macro but it is not copying anything when I click the button. I am probably missing a step.
It doesn't seem to be linked to the other workbook Tracking Update?

Please help if you can. I await your reply.

Hello hrhr2, I am going to go ahead an make some assumptions:

1) The master and tracking file both have a header row for the mail data.
2) Prior day/days data in the master file in columns A, B and C will not change, the only thing that changes is more data on new lines
3) The master file is already updated by the time you want to copy and paste into your tracking/update file

If this is the case, you can place two codes to knock this out pretty easily (I chose to do an Open event to automatically copy and paste your data). This means upon opening your tracking file it will go and copy your data for you right then and there. If you dont want this to happen, skip this code and assign a button to call the second macro below or even run an event timer.

Place below code in your ThisWorkbook vba project sheet

Private Sub Workbook_Open()

Call Sheet1.AutoCopy

End Sub

'Or if you want it to run at a specific time of the day try using:
'Application.OnTime TimeValue("08:30:00") "AutoCopy"

This next one is your copy and paste macro, place in the vba project sheet where data is held.

Sub AutoCopy()

Dim pasteTo As Range
Dim countRows, i As Long

countRows = Application.CountA(Range("A:A"))

Workbooks.Open Filename:="C:\Documents\Master.xls"
i = Application.CountA(ActiveWorkbook.Sheets("Mail").Range("A:A"))

If i = countRows Then Exit Sub

ActiveWorkbook.Sheets("Mail").Range("A" & countRows + 1 & ":C" & i).Select
Selection.Copy
Workbooks("Master.xls").Close
Set pasteTo = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
ThisWorkbook.ActiveSheet.Paste Destination:=pasteTo
Application.CutCopyMode = False

End Sub

Now in the above code, make sure to change tab name for Tracking Update from "Sheet1" as I assumed that was what it was since you didnt supply above. Also change path of file open for Master workbook.
This code worked for me, let me know if you need help with it.

 

[GRTorres]

Sure no problem. The code:

Sub AutoCopy()

Dim pasteTo As Range
Dim countRows, i As Long

countRows = Application.CountA(Range("A:A"))

Workbooks.Open Filename:="C:\Documents\Master.xls"
i = Application.CountA(ActiveWorkbook.Sheets("Mail").Range("A:A"))

If i = countRows Then Exit Sub

ActiveWorkbook.Sheets("Mail").Range("A" & countRows + 1 & ":C" & i).Select
Selection.Copy
Workbooks("Master.xls").Close
Set pasteTo = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
ThisWorkbook.ActiveSheet.Paste Destination:=pasteTo
Application.CutCopyMode = False

End Sub

should be placed in your tracking updates file. When the workbook is open, select the tab/sheet you wish to copy your data to. Now, at the top in the ribbon/heading, select developer and click on view code in the controls section. It should pop up a blank sheet in the vba. Paste the code.

Now, the tab/worksheet you want to copy to should have a name, Sheet1, Sheet2...., if you call it Data, change the Set pasteTo = Sheets("Sheet1") to Sheets("Data").

With that out of the way, you will need to designate your workbook path for your master file. That is on Line Workbooks.Open Filename: = you should place in parenthesis your path to your Master file here. The tab with the data on it should be called Mail per your description above. If all these are set and change correctly. You should be good to go.

 

[GRTorres]

Also, if placing a button, make sure to right click on it and then assign the macro to it. Not that you forgot this, but sometimes easily overlooked.

 

[hrhr2]

Thank you so much!! Got it to work. Appreciate it!

Also, if placing a button, make sure to right click on it and then assign the macro to it. Not that you forgot this, but sometimes easily overlooked.

 

[GRTorres]

Not a problem.