Copy data from tables into other tables, using criteria and VBA (with example)?

Hi all!

BIG example attached below, with LONG explanation.

Basically I would need to sort the tables from Data1 sheet and Data2 sheet into the tables on the Outcome sheet using VBA(!).

This sort is based on the Crop Location, but there is no Crop Location column in Data1 and Data2, only Crop Name, and on the Admin sheet there is Location for each Crop Name.

The data is then sorted into the 4 Location tables.

When I did it manually for the Expected Outcome, what I did was:

Go into Data1 sheet, and in the first row check the Crop Name, go to the Admin sheet, find that Crop Name in the Loc_Table, and check the location. If it is East, then I copy the data into the Outcome sheet EastTable if it is North then Outcome sheet NorthTable, etc.

Unfortunately, the copy the data is not as simple as copy the row and paste it, as the columns are mixed up, so its copying values from one cell at a time, into the relevant column in the Outcome table. (Notes in the Outcome tables are the Actual Notes in Data1)

Also I have two other columns in the Outcome tables, Trial/Non Trial and Care Type, and for these I also have the look up tables on the Admin sheet, and they should also be looked up.. Trial/Non Trial should just return a Yes or No, based on the Trials_List table on the Admin sheet, If the fruit type is present in the Trials_List table, then YES otherwise NO
Care Type should return the Care Type based on the CareType_List table on the Admin sheet, Check the Care Pack return the Care Type that stands beside it.
It could be a VLOOK Up formula standing in these cells maybe?

Next Row

Once all of Data1 sheet is sorted, go to Data2, and do the same, but make sure that there will be no duplicates in the outcome tables (for example Black Pearl Blackberry is in both Data1 and Data2 (Fruit Name and Fruit Type, both have to be criterias for no dulicates)) There is a lot less information in this table, (no seed reference, nationality, carer id) but thats ok just leave those fields blank, they will be entered manually after.

Also don't worry about the Growth cost, tax, invoice total, gross total columns in the outcome tables.

The idea is to press a button and magic happens and the data, is in the tables in the Outcome sheet looking like as in the Expected Outcome sheet. Its the sorting code that I am working on, not whatever data is there and not reports from it or so.

Now the sheets are locked for editing because they are not supposed to be edited (they are software generated tables). All tables have their own specific name, and I would like the code to refer to them by their names and the columns in the tables by their headers.

The reason I would not like to add helper columns to the tables in Data1 and Data2, is because those tables will be scrapped. Only the Outcome tables will be kept, and I wouldn't want to spend time generating data that I will then just throw away, if it is not necessary.

The Admin sheet can be edited, I am now thinking maybe having 4 different tables for the location look up, one for East one for North, West and South, would maybe make it easier? Not sure.

Sorry for writing a book about the problem, I was just trying to answer all questions before they would come up.

This question will be posted on Mr.Excel Message Board as well. Link will be included in this post later:
https://www.mrexcel.com/forum/excel-...ml#post5229112

Could someone help me with this please?