Excel 2007. Macro to move data based on dropdown list.

Hi all,

I have attached an example workbook (2007) of the solution I’m after. There are 3 sheets.

In Sheet 1 - the user will enter numeric data in cells B2:J2 (highlighted in green). Then they would click the command button which would then enter this data into Sheet 2 into the appropriate row. The appropriate row is defined by whatever ‘Organisation’ they have chosen from the dropdown list on Sheet 1 (cell A2). In this example it is ‘Organisation 5’. Once the user clicks the command button I would like the data on Sheet 1 to vanish (and the dropdown list to revert to ‘Organisation 1’ so that they can continue to enter more data).

The last part of this is that the user, at a later date, may be required to enter more and more data for Organisation 5 (for example). If this happens then I would like the data to be put below any previous data for that organisation (as shown in Sheet 3) – the new row is highlighted in orange.

I need code for the command button which achieves the above - can anyone help please?