VBA to copy a cell value from one workbook to another workbook automatically

Hi I have been struggling creating a VBA code that will do the following:

I have two spreadsheets and would like to copy the value in WB1 (column/row) AD26 which is “Y” based upon the person’s name in C3 (WB1) and the course name in D3 (WB1) and automatically open WB2 (C:\document\training.xls), which has one worksheet (“trainingcoursecompleted”) has all the people names (column A) and the courses they have taken (column B) and paste the value (“Y”) in the cell that matches the name in WB1 (C3) and course name D3 (WB1) then closes the WB2 automatically.

I am hoping you understand what I am trying to accomplish but if I am not clear please let me know so I can try and explain in more detail.

I am hoping that someone can help me as I am totally lost in how to create this VBA or if it can even be done.

Kris

As request please find attached 4 spreadsheets first two are BEFORE and the other two are AFTER. Ideally on the Verification-Template spreadsheet when a "Y" is entered into AD26 it would automatically open the TestTraining spreadsheet and put the "Y" under the name of person and the name of the Verification (in this case GIS New Business), automatically save and close the TestTraining spreadsheet

The actual worksheets to use on the Verification Template workbook is the VerificationA worksheet

I was able to create the below code which will open the other workbook but I do not know how to get it do it automatically when a "Y" has been input in a drop down menu or how to match the name of the person to the name of the verification. Any help is really appreciated.

HTML Code: 

The spreadsheet "test training" has all the names of the people in the company already in place (however if there is a new employee it would be great if the person's name would automatically get added at the end from the "BEPE-..." spreadsheet and the results inputted)
The spreadsheet "BEPE-....", every time a new training is started the trainer inputs the persons (trainee) name in C2 and the the name of course they are being trained in M2 and when the trainee pass the course a "Y" gets input in AD26 then the code should automatically open up the "test training" spreadsheet, find the person's name from C2 and the course name from M2 from the "BEPE -...." spreadsheet in the Test training spreadsheet and put a "Y" in the cell under the course name and person name, automatically save the "test training" spreadsheet and close the "test training spreadsheet.