Assigning Work to Teammates within Office

Hello,
We have developed a new team within my office that will be out processing all the work the rest of the office does. All of our work is done in an electronic system which is tied into a generated report to which I have tied my Excel worksheet. I know the basics of recording my own macros but I want to add an extra step or two to the below macro that will automate the assignment process. A template of the workbook is attached but obviously the connection won't work.

So far the macro text looks like this (I've cleaned up the spaces for length):

Please Login or Register  to view this content.

As you all know, this is refreshing the data connection and then filtering to only those results containing "awaiting correspondence" in the 8th column. As you can see in my example, there are various types of matches for "awaiting correspondence" that may result. I would like to include additional steps for the macro as follows:

1. Find all items in Column 8 (H), except those with "Meeting Complete Awaiting Correspondence", with a modified date (Column 7/G) that was the last business day (we tend not to have any business conducted on the weekends and the reports refresh overnight so Friday's approvals will go come to my team on Monday morning).
2. Assign in Column 9/I (which is currently used for another purpose which will be moved to Column 10/J) work to those members on my team, equally and randomly. I need the Macro to be editable or select from another sheet a list of staff that can be changed. We just hired a woman who will be leaving on maternity in 6 months and I need to be able to remove her name during her leave).
2a. If possible, I would also like to set one staff person as a senior who I could modify their assignments down accordingly for other projects. For example, I could say that the person with the senior designation gets 2/3 the amount of work of the others when we are fully staffed but remove this modifier when our new staff is on maternity leave.
3. After assignments are made, copy each team members assignments to the designated sheets after any data that already exists (I plan on pulling from that main report the assignments that remain in my staffs queue's so they have an idea what their workload looks like onto these sheets).
4. After assignments are made, count each new assignment given on the Assignment Roll-Up sheet in Columns C, E, and G respectively based on the type of assignment in the worksheet Assignments - Master List, Column C similar to the formulas you already see in those cells (new studies - look for starting with STU; modifications - look for mod*; CRs - look for CR*; reportable events - look for RE*).

I know this is a lot but this would automate about 1-2 hours of work that my senior is planning on doing every morning and would free this new team up to be more efficient right away. I appreciate any assistance this forum could offer.

Hi, Melvin.

Please take a few minutes to read the Forum Rules, and then edit your post to wrap your code with CODE tags.

Thanks.