VBA script to help sort and distribute 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.


Here is a dumbed-down version of my workbook.

Sales Data.xls



Can anyone give me some guidance?

Thanks!!

[werafa]

You can use a vlookup if there is an identification key for the row that you can compare - and use this as a 'copy/don't copy' test
a macro will always need to start with a blank memory - you will need to Identify rows to be copied, and then copy them using either a test or a string comparison routine

Can you use a pivot table instead?

[snb]

Are you familiar with autofilter and/or advancedfilter ?

[werafa]

A little, and it is fairly easy to steal code through the macro recorder for auto-filter at least. I think I have done this for advanced filter as well.

Once you get your code, re-jig it to use defined ranges instead of activesheet and selection - you can then be confident that you do pick up all the required data.

You can also manipulate sort and filter options in pivot tables - the recorder is half the story, and there are a few very helpful web resources out there regarding pivot table objects and ranges. the more I think about it, the more this seems like a job for a polain and simple pivot table. the bits you will need to automate are to update the input data range and possible to refresh data - everything else can be pre-set.