Shift Turnover Automated functions

[kranco]

Hello everyone, new to the forum and this is my first post.

I am a unexperienced excel user just starting out with developing tools to help me along.

I have developed a worksheet that I want to use to communicate between Supervisors on 3 shifts of issues and problems that need to be turned over to the oncoming Supervisor.

I am in need of a macro that at midnight everyday it clears off completed items (Drop down box colored green in my worksheet) and that on each Sunday transfers open items to a new worksheet.

I hope someone can either coach me on how to accomplish this so I can learn a bit about it or give me suggestions on how to approach it.

I am attaching what I have now: