Can I automate the duplication of a spreadsheet for 92 different people?

I have a template (attached) that I need to 'save' 92 times with the unique names in B5 for 92 different people.

I will need to do this monthly. Is there a way to automate that process with a list of the 92 people?

Let me explain the big picture....

Over all, I'm looking at a very time intensive process to create and then update these forms weekly for each person (after running numerous reports to gather all the various information needed) and I'm trying to find the best most efficient way to do it.....any recommendations?

I had thought of creating 12 tabs in the file, 1 for each month, and a summary page as well, to track the totals for each month at one glance. That way I only had 92 files instead of 92 people X 12 months = 1,104 files...but still.... and then at some point I KNOW the powers that be will want me to roll up the summaries to a company total....I am wide open to suggestions, but I do have to get moving on it as the data needs to start being collected next week....we've been developing the template and working out the numbers and formulas for a few weeks now.

Thanks for all your thoughts!

Carol

When you say save you mean as a separate workbook? Or separate Sheet?

I'm looking at 92 workbooks - because I think if I had 92 sheets it would corrupt.

I'd rather have the 92 workbooks with 13 sheets instead.

Ford - I do appreciate your thoughts and I wish it was that simple. I can tell you that there was a great deal of effort put into percents, points and weights to figure out how this was going to even work, what the bare minimum standard will be, etc (which have been modified again even since I posted this question). As for the data collection, due to software and other systems currently in place it is a convoluted mixture of manual processes and numerous reports out of a program that doesn't do data collection well (very simple collection but not layered filters that I need) - so the collection part of this whole process is going to be a big mess and several people - our software just doesn't support what we are trying to do and we hope that we will be able to drag our software (probably kicking and screaming) into doing what we need, but until we can get a working expectation, it means nothing to them. The looks are definitely the last thing, because as far as I am concerned, this is a very basic layout and simplistic cosmetics (and at that my boss is wildly impressed with the whole thing - go figure).

xladept - I haven't worked out the summary sheet yet, but your suggestion would save me tons of time.....if I did create the summary sheet, in addition to creating all the additional sheets, would it be able to link all the data between the monthly sheets and the summary sheet? That would be the moon and more! Your reputation to me would be over the top! (which it already is because you've told me what I think I should be able to do is doable!)

Thank you for all your help .... I am out of the office for the weekend now, so I will not be checking back in until Monday, but any ideas, again, are very welcomed. This is an amazing group of people willing to help out!

Carol

xladept - Setting it as a template didn't even occur to me - I do that in Word, so I'm not sure why it didn't. That's a great idea! Would I still be able to use the coding to create 92 workbooks from it? (Probably a stupid question, huh? ) I really do love automating as much as possible. I am almost ready to get to that stage of this project. We have one last meeting this afternoon to discuss final details and then I will need to get it ready for data collection for each employee for 1 week of August as a test and then the full blown for September.

Thanks, Carol

xladept - Your code only copies the 1 worksheet. I have 14 sheets that need to all go together, except for the 15th one that has all the names. How do I change it to copy the entire workbook? change Worksheet to Workbook?

Xladept,
It was originally, however, once the tally sheet was completed, we realized we needed it for 12 months and a summary page...so the actual list of caregivers is on Sheet15 - which I adjusted for that, but it was only copying the Summary page into each file...see attached, I have scrubbed it with Sesame Street again. I only entered a few names, there are 92 in reality. I did attempt to play with the code, but it wasn't working.....

The desired results is the entire workbook minus sheet15 for each of the people listed on sheet15...in the same folder the template is located in.

Thanks, C

xladept,
Thank you for this - I can see you put some work into it.

The first code works, but does not enter the name into the file.

The second code is hanging at the ws.Unprotect (Password) line - and if I remove it and change the Range to C4 (the cell on the Summary Sheet that is unprotected and the one everything else is linked too) it still hangs at ws.Range.....and if I move Application and wt.sheets.....delete to after the changing of the name, it still hangs at the ws.Unprotect or the ws.Range if I remove the ws.Unprotect.

ideas?

xladept.....
Wonderful! Let me share what I did to it.....

It took me some time, but I finally figured out that I had to enter the actual password (duh, right?) and it started telling me the password was not correct so I wondered if placing "" around the password would help and it did.

I had set up C4 on the Summary page to auto linked to the C6 on all of the other sheets to fill in the name, but once I figured out what you wrote this to do, I moved that C4 to C6 on the Summary Page and just let the code overwrite everything.

It does error at wt.SaveAs P & "\" & Wlist(r, 1) when the names run out, but that's ok, that just means I'm done and I can stop the run.

I really do appreciate your assistance and time! It's moving slowly, but faster than it would have taken to do it all manually and I can leave it running and do other tasks, so this is perfect! In the time it took me to write this message it completed 24 copies.

Hmmmm, 1 didn't save properly, I think because it had a "." after the middle initial at the end of the name, so I should have a few of those that will need to be corrected and re-run. It's all about the integrity of the data, isn't it!

Really, thank you again!

Carol

PJ - I am absolutely positive there is a better way! I didn't have time to figure out what that was because I had to have something ready this week to start collecting data. However, now that I have something to start collecting, I can work on a better way - because this is only going to get bigger as we add staff.

I've worked with databases so I get what you mean, but I haven't done so within Excel, so it didn't occur to me to do it within Excel. And the fact that I am dealing with 5 other office staff members that will be adding their pieces of data into the reports as well (hence the reason it is all locked down with the exception of the entry fields), left me with something that needed to be easy for them (they are very basic excel users at best).

I need a faster/better way to round up all the data out of my software as well. The reports my software provide are very simplistic and I need to group, sun, count, and compare in order to enter into the score card - not to mention changing the format of the date and time fields - the raw data is really raw.

I guest that is a long-winded way to say, yes, a better way, I am totally open to that and I'm open to learning how to do that (I've learned so much in the month we've been dealing with getting this started), I just need some direction from someone(s) more experienced in what Excel can do for me than I am.....because I do not have access to ACCESS.

Ideas? Carol