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

Would you consider a variation to the way you have the data organized on Totals?

Instead of having a name for each auditor going across the top would you consider having columns A to E

A = Auditor's Name
B = Date
C = Category
D = Call Type
E = Name of Audit

With a setup like this, we could use a pivot table to get yesterday's data with summary information at the top or maybe a second page such as number of audits done by an auditor, types of audits done, etc.

Then we can copy the results of the pivot table to a report file with the appropriate date stamp.

If you are concerned about data entry on the Totals page (having scroll down too far), I can arrange that on a page activate event, focus will be placed on the first open line at the bottom of the page and even autofill it with the current date. Since you have 2010, then the formulas, validations, etc. would copy down automatically.

There would be no need for 30 separate pages or copy and paste for the report. It will happen at the click of a button.

I have a question about data entry. How is the data entered? Does each auditor do his/her own entry and if so, how do you give them concurrent access to the workbook?

Would it work for you it each auditor got a daily workbook and filled in the information for that day? At the end of the day, the auditor puts the book in a "Pending" folder. The main worksheet has a program that reads the folder and extracts information from the workbooks and moves them to a "Completed" folder.

The Main workbook will have the capability to generate the daily workbooks for the auditors by name and with date stamp. E.g. Audits-Joe 161101. The workbook would then be primed to autofill the correct date in the date column and auditor name in the Name column. Each auditor would only have to deal with 5 columns of data, and the book will be primed on the last row for data entry.

This would also keep auditors from messing with each other's data (unless they open the wrong book).

So, at the end of the day, you would punch a button on the main sheet and it would gather information from the workbooks in the pending folder and create a workbook in the pending folder for each of your current auditors. The auditors fill the books out in place (meaning in the pending folder) and save and close them the following day.

I already have a project that does this kind of thing so I have the process worked out.

In that case, all bets are off. You can't use macros in a shared workbook.

What I am proposing is that at the end of the day you click a button and it does the following:

It gathers up all the workbooks in the Pending Folder and processes them: it collects and consolidates the data in the master workbook and moves the files to the processed or completed folder for auditing purposes and to make sure you don't double count anything.

It takes the new data and it makes a "This is what we did today" report that you can send off.

It creates new daily files for each auditor with the auditor's name and date stamp for the next day and puts them in the pending folder so they can fill them out.

This will be the point of failure: if an auditor leaves the file open or doesn't save it. The program is going to work with the latest file that is saved to the disk. I'll check around to see if there is a way to at least detect this condition and warn you.

I can further suggest that if you have administrative rights to your machine that you can schedule the machine do all this at say, 10 PM at night at a time when you are certain that the auditors are done with their work and get the new day's files out in plenty of time for them in the morning. Your report will be waiting for you first thing in the morning so all you have to do is review it and send it to your boss.

Hi taylorsm

Play with the Code in the attached. Click the "Show Form" Button. You'll need to sort out the Sharing Issue.