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

**Plumby** · 02-10-2012, 08:34 AM

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?

**buran** · 02-10-2012, 09:18 AM

Hi
I will, write something for you. Is it important that data appear in the order they were entered, or it is OK to add the new row at the bottom and sort the range by organization and date?
Buran

**Plumby** · 02-10-2012, 09:21 AM

Hello buran,

Thank you. I think that would be fine, it doesn't matter in terms of how they are ordered as long as all the 'Organisation 5's are together, the 'Organisation 3's are together and so on and so on.

**buran** · 02-10-2012, 09:42 AM

Hi
find attached file. macro is sheet1 module. I moved the list of organizations on separate sheet and defined a name for use in Data Validation for A2. I also set data validation for B2 cell (Date>01.01.2000).