Macro to format and track dates

Hello Excel Forum,

One of my many duties is tracking mail and due dates for all our departments clients. The client will send us mail and 1 month later we send them reports based on the items received. I have been working to create a Work Book that all of my department can enter in the dates they sent reports. I will still be checking in all mail however I want the Due Date I populate to show on a persons individual sheet. Each person has their list of individual clients that they are responsible for. Also we have another department called Response Team that also has access to all clients because they send reports to any client at any time. We have two different reports possible to send, Log reports and DQ reports. Log reports are dynamic and will change due dates from month to month. DQ reports are always due within the month no mater if the client mailed us something or not. DQ normally will be sent along with the Log Reports. Are you confused yet?

My main goal in all of this is to have all the personal sheets point to the main "Mail Check-In Due Dates" sheet and the "Response Team" sheet. I attempted to do this all with formulas and found myself buried in more work when I has to add or subtract clients. I am attaching a very close to real example with all the formatting and look I want. I could also not find a formula to add a client to personal lists based on the Rep name in column b and c on the main sheet. Also when its added it needs to be in alphabetic order with all the formatting included.

-To the Point-
1. Point each months Due Dates from "Mail Check-In Due Dates" to the assigned reps personal sheet and the "Response Team".
2. When anyone fills in a Date Sent column for the month point that info back to "Mail Check-In Dates" this also includes when "Response Team" fills in anything.
3. When a new client is added or subtracted it will fill in the assigned DQ and HOS rep's personal sheet and the "Response Team" in alphabetic order with all the formatting.
4. Billing sent will not be date but will be a "Y" for yes.

Once you look at the example you will get a feel for the formatting and hopefully understand what I am trying to explain above. If you need further information I will be glad to inform to the best of my knowledge.
Also I am working to get the company to move everyone to Office 2007 so this project once completed will allow more of a push into the 2007 world.
Thanks for your help and I will appreciate any help on this project of mine.

HI,

I modified this so that you can select the Rep and Then Add the Client for the Rep.
Check the message in the Employee sheet.

Hi,

If you go to worksheet1 code module you can remove

Please Login or Register  to view this content.

It err'd because I remove the the sheet for the "HOSREP".
Also when you click on the debug it will take you to the line of code that erred.