Filter Data based on list and email just that data to specific user

I have a spreadsheet that is updated nightly for all the sales orders for the current day. In column C is the sales reps code for the customer orders that are assigned to that sales rep. What I need to do is build some code that will copy only the rows (and headers) for each rep and email the file to that rep. I have a separate tab in this file that has a list of the codes and the reps email addresses. So I need to find a way to copy just their orders for the day and email them the list. The sales rep codes are not very cut and dry though. There can be multiple reps assigned to a specific customer account. And they are identified in column C like this: T-V. The "T" is one rep and the "V" is another rep. If there is only 1 rep assigned to a customer then there will only be one code in column C like this: "T". There are many different variations of these codes since we a few sales reps. So I need to use this separate list and find all the records whether it be the first letter code or the second letter code in column C and copy those rows to a separate file and email it to that rep. So there could be rows that go to multiple sales reps. How can this be done using VBA code?
LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
It would help if you attached an example workbook
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Here is a sample file. The tab called "SO" is the list of orders for that given day. The tab called "Rep Codes" is the list of codes and the email addresses for those reps. If you need a better explanation I can detail it out more.
Daily-SO-Report.xlsx
Roy CoxGroup Finance ManagerCommented:
How do you want to send the details - in a workbook attachment or the body of the email?
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Roy CoxGroup Finance ManagerCommented:
I've written some code to create a temporary workbook for each rep and email it to the rep and cc any additional rep.

Check this out and post back with any comments
Daily-SO-Report.xlsm
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
That's what I'm looking to do but is there a way to group them into 1 file for all the codes for a specific rep? For example....The rep who has code "Y" will get 1 email with the file that has all the records that has "Y" in the rep column. So all the single Y records and the Z-Y records in one file instead of separating them and sending 2 emails.
Roy CoxGroup Finance ManagerCommented:
The code filters the orders by rep and creates a separate workbook with each rep's orders, it alsso cc's a second rep if there is one.

Have you tried the code? Send the emails to yourself
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I have tried it but it makes a separate file for each different code. I would like it to have all the records for each rep in one file instead of sending them a separate file. There is a file created for the Y rep code and then another file for the Z-Y rep code. I would like to have all the Y codes in one file and sent to that rep. They would prefer to just get 1 file for all their records. Make sense?
Roy CoxGroup Finance ManagerCommented:
You cannot filter Y or Z from Z-Y.  AutoFilter will only filter by the actual entries, so Y, Z oy Z-Y.
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
You can't do an autofilter that will only show the rows that "contain" the letter Y?
Roy CoxGroup Finance ManagerCommented:
I'll try to amend the code and filter criteria. I'll get back to you as soon as I can
Roy CoxGroup Finance ManagerCommented:
Try this amended workbook. It should do what you want, it finds all entries containg say "Y", Y & another rep or another rep and Y
Daily-SO-Report-2-.xlsm
Roy CoxGroup Finance ManagerCommented:
I need to do more to the code, I forgot to try a wild card
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
ok thank you. I will hold off on testing the last file you uploaded.
Roy CoxGroup Finance ManagerCommented:
As I first thought, I can't get AutoFilter to work as needed. I'll try a different approach
Roy CoxGroup Finance ManagerCommented:
Just an update. I have the code working how I think that you want it to. I'll tidy it up and post back tomorrow. Sorry about the delay but the day job is really hectic at the moment
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thank you for the update. No worries about the delay. I appreciate all your time and efforts!!
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Hi Roy...just wanted to check in to see how you were making out. Thanks again!
Roy CoxGroup Finance ManagerCommented:
Sorry, I thought I had uploaded it. Try this out
Daily-SO-Report-3-.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
No worries. That's exactly what I was looking for! Thank you very much for all your time and effort. I greatly appreciate it!
Roy CoxGroup Finance ManagerCommented:
Pleased to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.