Need help in creating a marco/formula/VBA script to sort sales data.

[Chavis339]

Hello all!

I am having some difficulties in getting Excel to do what I want. I have a workbook for sales and all my teams information is in one big sheet. What I want to do is find every row that has "Salesman's Name" in a column, copy a range with that row and paste it into another sheet. Here's the catch, when the macro is run, I do not want it to re-copy and paste the data it already has. I want it to look for new data entered on the data sheet and add it to the growing list on that salespersons indiviual sheet.

For example:

A B C D E F
1 Date Product Client Salesperson Amount Commission
2 4/1/2014 ABC Bob Ryan $2,000 $200
3 4/8/2014 ABC Shirley Steve $1,500 $100
4 5/4/2014 XYZ Brad Melissa $2,400 $500
5 5/6/2014 PDQ Jeremy Ryan $4,200 $750
6 5/9/2014 XYZ Pam Melissa $800 $150
7 6/7/2014 ABC John Ryan $1,250 $250

I want to be able to search for all of Ryan's sales and populate them into his sheet. But only those specifc cells, because his sheet will have additional data in Column G that needs to stay. If I perform the macro/formula/VBA script, on 5/10/2014, for Ryan, it would pull data from Row 2 and Row 5, and only Columns A through F. When I run it again on 6/10/2014, I would like it to add just Row 7 to the growing list on his sheet instead of replacing the data that was already transferred.

Can anyone give me some guidance?

Thanks!!

[Richard Buttrey]

Hi,

Are you sure this is the best way of proceeding? Don't forget that you use both Data (Auto) Filter and Advanced Filter to filter a database in place. Or you may find a Pivot Table will be more efficient and useful.

What I'm saying is do you really need a separate sheet for each individual when you are able to manipulate the main database.

[Chavis339]

I do need seperate sheets. Both for their reporting purposes and mine. I was looking for a way to automate this step because sorting it all and transferring the data by hand is very time consuming.

[Chavis339]

Here's a edited version of my data sheet. I want to filter for a sales person, take the whole row where their name appears and paste it into their own worksheet.

Sales Data.xls