Update Workbook 1 using information from Workbook 2 while preserving information on Workbook 1

[JenJarchow]

Hello!

I am trying to design a VBA that will allow me to update Workbook 1 (Site Master Tracker Template) from Workbook 2 (SUSAR Master List_DO NOT EDIT). Easy enough, except I use Workbook 1 to cross check files at a location and when I update Workbook 1 with the new information from Workbook 2 (usually updated documents) I want to preserve the information in Workbook 1 and only add the NEW information from Workbook 2.

In Workbook 1 the information I want to preserve is found in columns A-K. In Workbook 2 the information I want to add are the new rows that have been inserted and colored red. Ideally I'd like to insert the new red rows and leave the other rows untouched. I want to be able to continuously update Workbook 1 whenever Workbook 2 has been updated with new rows. I hope this makes sense! Sorry I can't upload the workbooks themselves I hope the pictures suffice!

 

[JenJarchow]

Oh wow this is ALMOST perfect! I just ran it and it preserved the information that was already present which I love! It also created the "Yes" item like you said. However it did not capture all of the new "red" documents from the source workbook. It captured the document in red listed in row 109, but the other four docs in rows 118, 119, 144, and 171 it did not catch. I've attached a jpg showing the filtered four non-transferred docs, and another showing documents around those that did not transfer. What are your thoughts?

I'm so excited! This is so close to being perfect! :D

 

[gordsky]

Oh wow this is ALMOST perfect! I just ran it and it preserved the information that was already present which I love! It also created the "Yes" item like you said. However it did not capture all of the new "red" documents from the source workbook. It captured the document in red listed in row 109, but the other four docs in rows 118, 119, 144, and 171 it did not catch. I've attached a jpg showing the filtered four non-transferred docs, and another showing documents around those that did not transfer. What are your thoughts?

I'm so excited! This is so close to being perfect! :D

Do the "Document Names" of the lines it has missed appear in the destination workbook. if no then this is why it hasnt moved across.

 

[JenJarchow]

Hello!

Two of the four, red document names do appear in the destination book (because its the same document but a newer version number) and those transfer over (yay!). The other two are unique documents added to the list later on and need to be checked at the location. They do not appear in the destination folder. It was my hope that all new documents, with a date in column k would appear in the destination folder regardless of name. Is there a way to have the code move rows into the destination workbook by searching for color? Or by an entry in column K?

 

[gordsky]

Hello!

Two of the four, red document names do appear in the destination book (because its the same document but a newer version number) and those transfer over (yay!). The other two are unique documents added to the list later on and need to be checked at the location. They do not appear in the destination folder. It was my hope that all new documents, with a date in column k would appear in the destination folder regardless of name. Is there a way to have the code move rows into the destination workbook by searching for color? Or by an entry in column K?

This is why you need to be specific in your queries. It is entirely possible to move them across (but you said UPDATES). The issue would be what section do you want them placed in as I notice that your destination workbook groups documents by column A

 

[JenJarchow]

This is why you need to be specific in your queries. It is entirely possible to move them across (but you said UPDATES). The issue would be what section do you want them placed in as I notice that your destination workbook groups documents by column A

I'm sorry I wasn't clearer when I originally posted and I appreciate you sticking with me through this!
Not column A. I'd prefer column B which is a bit more specific, however there may be issues with duplicate values because as you can see in my previous posts there are often times multiple docs in the same area.

So continuing on then we have column C. There are documents named the same, however, slightly changing the name would work to differentiate them and prevent any issues with duplicates not being picked up. So let's try column C. Ultimately I'll defer to you though, whatever is easiest to code in your opinion!