Copy specific cells based on test to next free row on sheet 2

Hello All,

I have a multisheet book that I need specific cells copied from one sheet to another after testing a specific cell and it needs to be added to the next available row.

Sheet 1 has columns a:v with potential data and rows 5:22, 24:45, 47:61, 63: 80, 82:97
I need to test cells in columns C, G, K, O and S which are dates. I need to test which of those columns have the most recent date. I then need to copy the cells from that row for the following columns: A,B, <column with most recent date> to Sheet 2 to and add it to next empty row. The data will always be entered from left to right when added and they will be in chronological order from left to right, oldest to newest.

Sheet 1
Eg. A B C D E F G H I J ...
1 name # date score pass action date score pass action
2 test 1 12/5/11 80% Y N
3 TEST 5 11/7/11 72% N Y 1/5/12 98% Y Y
...
In the example above I would want A2:F2 copied to the first empty row on Sheet 2, A3,B3, G3:J3 to the next empty row on Sheet 2.
It needs to test all of the rows each time it is run. It cannot overwrite any data on sheet 2. I also do not want duplicates copied, only new data should be copied.

Sheet 2 would look like this from the above example
A B C D E F
1 name # date 60 Days 90 Days Complete
...
N test 1 12/5/11
N+1 TEST 5 1/5/12

I am really new to VBA and am not sure how to tackle the problem. I think a manual execution would be best as I need several cells populated before the data is copied otherwise I would then also need to test to make sure the next several cells are populated. Any assistance will be greatly appreciated.

Thanks

You could use this code:

Please Login or Register  to view this content.

Regards,
Antonio