How to copy a Word document 30 times and rename it

[Newbuilder]

Scenario:
I have a master document for writing a school report on a pupil.
This needs to be duplicated for every student in the school and the file name needs to have the childs surname and first name on it.
I can pull the surnames from the Management system in school into column A of Excel and forenames into column B.
Is there a macro that can take the word document, copy it and rename it using data from column a and b and then replicate the process for each row in the spreadsheet?

To future proof this sheet, it would be great if it could ask the user to select the file to be copied and then the folder that this will copy in to so that I can give this macro (embedded into Excel) to admin to run.

Hope someone can help or I might have the files names by the times the students leave the school (7 years!)

[JBeaucaire]

Will this be PCs only or some Mac as well, I mean all the computers running this?

[Newbuilder]

Hi
Thanks for the reply, this would be on Windows PC machines.

[macropod]

What you're describing is a mailmerge. See: https://support.office.com/en-us/art...f-932c49474705
No VBA programming is required for this.

[Newbuilder]

I am familiar with mail merge but did not know that it can copy a file 30x and then rename a file name 30 times using the names from a spreadsheet . I thought it only merged within the document

So master report.docx will be copied to a destination folder then renamed as bloggs john report.docx

[JBeaucaire]

I read the question as an Excel VBA question.

How to take a list of names in an Excel Spreadsheet and use those names to create named copies of a Word Document, selecting the template file to make copies and selecting the destination for the copies being created with the new names.

[Newbuilder]

@JBeaucaire 👍

[macropod]

If you want to automate a mailmerge, to output separate documents instead of a single document (including giving the output documents names corresponding to fields in the source data), see Send Mailmerge Output to Individual Files in the Mailmerge Tips and Tricks threads at:
http://www.msofficeforums.com/mail-m...ps-tricks.html
or:
http://windowssecrets.com/forums/sho...ips-amp-Tricks

[JBeaucaire]

This macro does what you've described, assuming the sheet name with the student names is called SHEET1, else you can change that in the code. With this macro you select any file to COPY and the select any destination folder.

Please Login or Register  to view this content.

[Newbuilder]

This looks brilliant and I will test it out as soon as I return to work. Pushing my luck now but as all pupil names will come into the spreadsheet is there a way to say "put into folder named in column c"

This will save us either copying and pasting each class and running the program or running it all then having to choose who should be in what class/form.

Column c would contain the class name and there would be a folders set up with these names within the directory structures. Warning: we would still need the select destination to get tell it the parent directory and then column c would tell it the sub directory within this selected parent directory

Thanks so much

[Newbuilder]

Hi, it goes through all the prompts but doesn't do the copying and pasting of the new file.

File uploaded to show you.
PS. The copying and pasting of data will always create headers in Row 1 so it would be great if it could be amended to look from row 2. Don't worry if not, we just delete a file.

[JBeaucaire]

It works! Here's a video, I just opened your file and ran it...

https://www.screencast.com/t/FOsNG0KDPK

[Newbuilder]

It works! Here's a video, I just opened your file and ran it...

https://www.screencast.com/t/FOsNG0KDPK

So it does, must have not saved the file first after adding the data.

This is absolutely amazing.


I have managed to add a force save and pop up Success box.

[JBeaucaire]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[macropod]

FWIW, the mailmerge approach can also populate the document with content from your worksheet (e.g. insert the student names) without the need for more code and, with a minor code change, save to different folders

[Newbuilder]

I've Broken it, what have I done!
It does not ask for file locations etc

Capture.PNG

[Norie]

Goto Tools>References ...
Are any items (libraries) marked as MISSING?

If there are note their names, uncheck them, save the file, and close Excel.

Now reopen the file and try the code again.

[Newbuilder]

Can I get the order for Excel (2013) PC please I don't have a tools

[Norie]

It's in the VBE which you can open using ALT+F11.