Excel 2007 : code that copies and pastes depending on value contained in a cell, for multiple rows

Hello all

This is a difficult one to describe so I've attached a workbook to make things a bit clearer. In Sheet1 of the workbook, cells C6 to C10 contain some letters, and next to each cell is a dropdown box with the options yes and no. In Sheet2 cells C6 to C10 are set equal to their equivalent cells in Sheet1.

I want to have a code for Sheet1 that will do the following:

If, for example, yes is selected in cell D6, then cells C6 and D6 are copied and inserted into the row directly underneath, but C7 will contain "" instead of the letter a and D7 will contain no, and in sheet2 cell C6 is copied and inserted directly underneath, with cell C7 containing "". If no is now selected in D6 then the new row that has been created in both sheets is deleted. If no is selected from the start then nothing happens. And the same is true for the other cells that contain letters when yes or no is selected. So it could be the case that yes is selected for all 5 cells, so the code would need to create 5 new rows. Or there are 3 yes's and 2 no's, so 3 rows need to be added etc.