Macro to Use Multiple Cells in Workbook to Create Code Cell in another Workbook

I am looking to create a macro that will look at cells in one workbook and create one code in another workbook. The EFCodeCompare workbook has the information for the codes and the EF-FinalBook has the workbook where I'd like to add two columns (ItemCode and Classification) The item code looks at the EFCodeCompare for the cells in "Spec" and then the "Class" and then the "Size" example 5EE-0450-0020 and pastes it to the corresponding row under ItemCode in EF-FinalBook. It also looks at the "Desc" row and adds the new "Class" Row. I hope these two workbooks clear up what I'm looking for...

Thank you!!

Hi
I cannot see any relationship between the two Workbooks so how do we know what codes to pick up to generate the new code you are after?
Tony

Sorry for the confusion, the row with Spec in "EF-FinalBook" should look for all items in "EFCodeCompare" with the same spec. It then looks at the Desc in the "EF-FinalBook" and the Description in "EFCodeCopare". The Description gives the second part of the code (usually 4 numbers) and also adds the column for the Class column in "EF-FinalBook". The final part of the code is from the NPD column in "EF-FinalBook" and looks at the Size column in the "EFCodeCompare" and gets the code for that. Basically there are 3 parts to the code xxx-xxxx-xxxx. The first is the spec, the second is the description code, and the third is the size code. I can create a flow chart if that would help?

Hi
The problem I am having is that there appears to be no logical relationship between the two Workbooks. For example:
EF-FinalBook : Row 8 : Spec = 6EEP
There are 11 of these codes in the Compare Workbook - how do I know which one to pick up?
Also the 4 digit number is in the Code column NOT the Description column!
And there is no Size against any of the entries of 6EEP.

Without logical relationships between the two worksheets we are stumped .

Tony

Here is a flow diagram of what I am trying to work out...

ProgramFlow.JPG

Hi
I understand what you are trying to achieve however let me see if I can explain the problem more clearly.
If you take each row in Columns A to D in the ColumnCompare workbook each row would be one record. However in columns E and F the entries there bear no logical relationship to each row in Columns A to D. In fact Rows 24 to 35 in Column E and F have no Size or Code. So the problem remains as to how we link the correct size to the correct record in columns A to D. We can generate a code quite easily but it would be pure guesswork.
Hope this makes sense.
Tony

Thanks Tony,

I understand...what if I take each section of rows, copy and give it the possible sizes so they have a relationship with the information in the rest of the row? Something like this revision to the sheet?

EFCodeCompare-Rev1.xlsx

Hi
We are getting there .
In the Code Compare book there is no entry under 6EEP that matches the NPD2 entry of 0.3125 in so a code can never be generated.
This should explain why there needs to be a match for each field in both Workbooks.
Tony

Hi
Please find herewith an example of how it could work once you have all the correct details in your Workbooks. I am out of the country for a week from tonight and did not want to leave you without a possible solution. Let me know if it provides you with what you need.
Good luck.
Tony

After playing with this one, I found that taking out the R1C1 after ActiveCell.Formula did the trick... Thank you!!