Pulling multiple (and varied #) of rows from one sheet into another

[kmlloyd]

I have a sheet (sample attached) that has a Data sheet that we import raw data into. I have created a Payment Summary sheet that pulls from this Data sheet and another sheet that I didn't include because it's not part of this problem.

What I would like to see happen is starting in row 16, excel would look in the "Data" sheet and pull the information for the employee listed in the Name Field (John Smith).

In my own copy, I have a drop down in D5 with all of the sales people's names, and I want it to pull the info based on who's name is in that field.

The amount of rows that it needs to pull could vary from 1 to....infinity I guess, but realistically a few hundred. The number of rows varies month to month.

There is a "solution" tab that shows what I want it to pull.

Any help is greatly appreciated. I hope this was a clear enough explanation.


eta: I pared down the Data sheet in this example. There are other rows that are in-between the rows I need for this summary that is needed for other functions....Not sure if that's relevant but I thought it might be important.

[davesexcel]

A simple loop would work for this.
Enter a Sales person number in D6, and hit enter, the correct data should appear.
This is VBA driven so you will have to enable macros when you open the workbook.

[kmlloyd]

Great, this worked in the sample, but when i try to replicate it in my actual sheet, it doesn't work...I've attached most of my actual workbook...I didn't post it all previously because I was in the process of making changes. Is there something I've done wrong in copying the code over?

Also...Is there a way to have it look at the value of cell D6 even if I have a vlookup there that pulls from D5? I'd rather not have to type the sales person # as I have created a Validation List that links back to another sheet with all of the Sales Person names on it, along with their numbers....because I don't know all the numbers by heart and I need to take this sheet, select a sales person name and then email it to that person.

[davesexcel]

Hi,
Try this, select the name from the dropdown menu, the code should then kick in.
Right click on the sheet tab and select "View Code", this is where the code is located to activate the macro GetData, it is called the WorkSheet Module.

[kmlloyd]

Hi,
Try this, select the name from the dropdown menu, the code should then kick in.
Right click on the sheet tab and select "View Code", this is where the code is located to activate the macro GetData, it is called the WorkSheet Module.

Thank you, this is great. I can't seem to be able to replicate this in another workbook...and it's just my lack of skill with VBA...even if I try to update the worksheet references it doesn't work (in one of my other sheets, the Incentive Payment Summary is Sheet 10). But I've decided just to copy the data from the other sheet into this one and save it as new. Then I just need to use this one going forward.

Thanks for your help - this is fabulous!

[kmlloyd]

OK I hate to be a pain...is this an easy code to break down for me? I'm being asked to pull different data, and not to pull some that I was pulling before...and to do this for a couple of other groups....but I can't figure out the code. If you could break it down for me so I can replicate it, that would be fabulous.

[kmlloyd]

bumping as I need some more help with this. I now have a new report, which I need it to pull in all the data for anywhere from 2-4 Territory Managers into one sheet. In the code, where it references Set num = ws1.Range("D6"), I need it to look at a range of C6:F6, which will have 4 different TM #s (see sample) and pull the data into this report for all 4 of those numbers.

Is this possible?

[kmlloyd]

bumping to see if someone can help.

[Whizbang]

I would suggest reading up on MS Query. This is a useful query tool that Excel uses and behaves similarly to Access. Using MS Query will eliminate the need, in this case, for VBA.

I recently made a sample workbook with a query in it.
http://www.excelforum.com/excel-gene...ase-query.html

[kmlloyd]

thank you I will read that.