dropdown & macros

[javvyjavjav]

Hi folks
i'm looking for a bit of help using macro's on Excel 2010.
what i'm trying to do is create a macro that will copy a range of cells from one sheet to another depending on a option being selected from a drop down box. I've tried to use formulas but without success.
sheet 1 contains a list of approx 20 people with rows containing sales figures per week.
is it possible to have a macro that will copy the rows to sheet2 depending on the dropdown?
The drop down has already been setup with people's names

e.g.
if sheet 1, cell a1 (with data validation setup) dropdown contains "mr smith", copy sheet2 row A1:A9 to sheet 1 cell a2
or
if sheet 1, cell a1 dropdown contains "mr cooper", copy sheet2 row B1:B9 to sheet1 cell a2

etc for each name in the dropdown

the idea is so that i select a dropdown and it copies the sales figures that match the dropdown name, if i then select another dropdown, the corresponding figures are copied to the same place

 

[Vidar]

Hi

Here's a link to a workbook that gives an example on how to solve this with just formulas.
https://dl.dropbox.com/s/xd2irot1z2l1utr/dropdown%20%26%20macros.xlsx

Vidar

 

[javvyjavjav]

excellent Vidar thanks for your help! thats sorted the issue pefectly!! Many thank!

 

[Vidar]

You're welcome javy . Thanks for the kind reply.

Vidar