transferring data between two workbooks

[JulieD]

Hi

looking for ideas ... i have two workbooks ... the first covers the progress
of items through a production process recording the dates at which each item
passed through each of five stages. The second takes those items which made
it through the first five stages and records the dates at which they pass
through the next two stages (and other related information). We're looking
at about 15000 items over a 12 month period.

I now need to automate the transferring of the "completed" items from the
first book into the second, making the process as automated as possible. My
thoughts are to write out to a third workbook the items when they pass the
fifth stage populating another column to say that they've been transferred.
I would like this to happen only once a day ... but as this needs to be
"user independent" i might have to do it on the workbook close event (or
similar). What i was thinking was that if the third workbook existed then
the new records need to be appended to the ones already in there. Then on
the workbook open event of the second book i was going to have code to read
the new values into the workbook and append them to the end of the existing
records and then delete the third workbook.

Is this possible? Is this the best approach? or can someone else recommend
another approach (no using Access isn't possble, nor is combining books 1 &
2).

Thanks
JulieD

 

[Nigel]

Assuming the already transferred items remain in workbook1 you need some way
of either recording the transfer has already taken place in workbook1 - set
a flag against the row, then just copy paste each new record meeting your
criteria directly into workbook2 OR copy all that meet the criteria and
dedupe inside workbook2. I would choose the first option.

Cheers
Nigel