Code to find and copy specific data

Good afternoon Excel Forum!!!

Everyone has been extremely helpful so far and I hope they can help with this also!

I would love to find some code that will take the data from C2, and paste to a different workbook based on the information in A2 and B2. I have attached samples of the workbooks I am trying to work with("In" & "Out"). I want to copy the serial number in column C(serial Number) from the "Out" workbook and paste into the "Serial Shipped Column in the "In" workbook, based on the corresponding data in Columns A & B. Workbook "Out" has the sheet name as "Today's Date" and is what we use as a packing list for the devices being shipped back to the customer and is used to validate the box labels on the devices being shipped. The workbook labeled "In" has worksheets that are labeled based on the RSA numbers of the shipment received from the customers. It is also formatted for duplicates based on the range of the item numbers on each sheet. So if the label scanned was correct, then when you paste the serial number from the "Out" workbook to the corresponding first blank cell in the correct RSA worksheet for that item number, then it will highlight red.

I would like to be able to highlight the serial number on the "Out" workbook if the following occurs...
1) The serial number does not hightlight red or if it highlights green
2) The RSA does not exist on the "In" workbook
3) The Item number does not exist on the RSA sheet in the "In" workbook for the serial trying to be validated
4) No Blank cell to paste the serial number on the "In" workbook

I look forward to either your coding skills or your ability to point me to the correct resources to get this process completed.


Thanks!!!

Dan

JapanDave,

I have attached a sample of what I would like the outcome to be.

Now, I have 2 desired outcomes...

1. To have the workbook titled "In", to be updated with the data from the workbook titled "Out." The "In" workbook should be updated to reflect the outgoing shipment of devices back to the customer. So the workbook may show that the customer is due back 15 total devices before the outgoing shipment, but once this validation process occurs, then maybe only 4 devices are due back to the customer relative to the "RSA" received. In simplest terms, we copy the serial number from the "Out" worksheet, find the RSA worksheet in the "In" workbook that corresponds to that serial number's RSA on the "Out" workbook, then find the right item number relative to that serial number and paste the serial number in the "In" workbook in the first blank cell in the "Serial Shipped" column on the "RSA"worksheet.

2. Have the serial number highlighted on the "Out" workbook, if the following occurs...
a) There are no blank cells for that part number on the RSA worksheet relative to that Serial number on the "Out" workbook
b) The serial number gets highlighted green on the RSA worksheet, in the "In" workbook
c) The serial number doesn't highlight red on the RSA worksheet in the "In" workbook
d) The Item number or RSA does not exist

I believe I have explained this as best as I can unless you were next to me and seeing it in action.

I have attached the desired outcome workbooks and also attached a revised "Out" workbook. The original one I posted, had the wrong column headers and this sample workbook is corrected.

Thanks!!!!!

Dan

Can anyone given give me some direction with the above issue? I have been thinking about this a lot and was thinking that this process would probably be similar to a VLookup function. The only difference is I would to paste the data in column C from the "Out" workbook to the appropriate "In" Worksheet based on the criteria of Item and RSA (or Column B and A respectively). I have to no problem experimenting if I can get some direction as to where to start or what the code should include...which I think is going to consist of a few If/Then statements.

Thanks!!!!!

Lets see if anyone has some ideas on this....

Does ANYONE the best way to code this? The code would need to go row by row. Starting with row 2, find the data that is found in "A2" on the "Out" workbook and find the sheet name that matches, then find the item number listed in column "B" of the "Out" workbook and find in the sheet just found in the "In" workbook and then paste the serial number is column "C" of the "Out" workbook in the first blank cell for that item on that sheet in the "In" workbook.

Any ideas to test or resources to figure this out would be a HUGE help!!!!!!