Need to make many copies of a workbook all with different names

[gibson2503]

I work in the fiscal department of a hospital. Every 6 months, I have to audit the timekeeping of every other department in the hospital. I have a blank one sheet workbook in Excel 2007 that is basically my audit sheet and report. We have over 100 departments to audit each time. How do I instantly make many copies of the workbook, each of them being named by the department name? Is there a code that will do this? Perhaps take all the department names from a list that I have?

[Tinbendr]

And how are you going to distribute this? EMail? If so, you could rename the same file with each dept name and attach it.

You could send a blank workbook to each dept with instructions on renaming their own WB with the proper name. (That's how we do it.)

[gibson2503]

And how are you going to distribute this? EMail? If so, you could rename the same file with each dept name and attach it.

You could send a blank workbook to each dept with instructions on renaming their own WB with the proper name. (That's how we do it.)

I will be sending it out by email. However, I don't want the recipients to be able to change anything. I figure I can just protect the workbook for that. As far as renaming, or doing a save as, that's what I have been doing thus far. Kinda tedious with how many I have. I have files saved in a folder on my hard drive that I will keep the working copies in. I also have a list of all the department names in an Excel 2007 work book. I was hoping there would be a way to maybe open the original blank copy, then click a button or run macro, and have it save 100+ copies, each with a department name from the list I have. This way I can pull up the workbook and go directly into the audit intead of doing a save as or rename each time.

[Tinbendr]

In the workbook that has the list of department names, create a audit worksheet and name it AuditForm

Please Login or Register  to view this content.

[gibson2503]

I get

Run-time error'1004': Method'SaveAs' of object '_Workbook'failed

[Tinbendr]

Please Login or Register  to view this content.

[gibson2503]

Same error as above.

[Tinbendr]

Where is your list of departments? Sheet name and range?