Macro to copy a sheet for each weekday of the month and rename it as such.

[taylorsm]

I have to send a daily log of what reports my team did for the previous day. So My team logs it all on one page and just builds on top of the previous days. This fine and works, but I would really like a cleaner and easier way. right now I have to copy and paste the log for each auditor into one sheet. Not difficult, its only six people right now, but that's not the point! Another concern I have is that by the end of the month, you will have to scroll a little ways to get to the bottom of the sheet.

So I have two options, one I can make 30 individual sheets and each day they update that single one and then I can just send that one sheet off. Or, they just add to a sheet each day and I have to sweep it for the previous day.

If I go with option 1, can I run a macro that will make a copy 30 times and rename it with the dates of the month? It would be easiest if it could identify the month without editing the macro, or if a popup asked for which month and only did weekdays. (this way if I am not the party to run the report, someone else would be able to manage it and not need to edit a macro)

Attached is the report i use. Totals is the sheet with the whole month, and Sheet1 would be what would copy over in order to send yesterday's activities.