Create multiple copies of a work book and rename each book as to a set list

[Hdivision]

Hi All
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Thanks for taking the time to review my post, I am absolutely confident that my question has been posted and answered, I apologize in advance if that is the case but I did try and look for the answer.
<o></o>
I have a workbook which has a bunch of sheets with data on them, I use the sheet as a template and for each new customer I copy the workbook and rename as per the customers reference name, I then place the work book within the customers respective folder.
<o></o>
My question are:
<o></o>
Can I use a macro to duplicate the template workbook and then rename the book as per a list or as per made folders?
<o></o>
This might be a bit too much but would a macro be able to rename the template and place the newly created template into specific folder?
<o></o>
If I am able to do the above and you share the macro with me, I will take you out for lunch the next time you are in <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:City w:st="on"><st1lace w:st="on">Brisbane</st1lace></st1:City>
<o></o>
Appreciate any help and on a last note “GO THE ALL BLACK RWC 2011”

 

[Weaver]

Try this:

Sub duplicateTemplate()
    fldr = "c:\local\myfolder\"
    tmp = "template.xls"
    With CreateObject("Scripting.FileSystemObject")
        Set listrange = Range("A2", Cells(Rows.Count, "A").End(xlUp))
        For Each fname In listrange.Cells
            MkDir fldr & fname
            .copyfile fldr & tmp, fldr & fname & "\" & fname & ".xls"
        Next fname
    End With
End Sub

Given a list of file names in column A, a folder called myfolder otherwise empty apart from a file called template.xls, this ought to do what you're after - create a set of folders and copy the template into them, changing its name as it goes. you can change all the paths etc. This should get you started, if you have any more questions, let me know.

HTH

 

[Hdivision]

Hi Weaver

Thanks I will try and run the macro this morning.

 

[Hdivision]

Hi Weaver<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
<o> </o>
I am having a run error<o></o>
<o> </o>
Run-time error “53”:<o></o>
<o> </o>
Sub duplicateTemplate()<o></o>
fldr = "c:\local\myfolder\"<o></o>
tmp = "template.xls"<o></o>
With CreateObject("Scripting.FileSystemObject")<o></o>
Set listrange = Range("A2", Cells(Rows.Count, "A").End(xlUp))<o></o>
For Each fname In listrange.Cells<o></o>
MkDir fldr & fname<o></o>
.copyfile fldr & tmp, fldr & fname & "\" & fname & ".xls"<o></o>
Next fname<o></o>
End With<o></o>
End Sub<o></o>

<o> </o>
<o> </o>
I changed the yellow highlighted to the Dir locations on my computer<o></o>
<o> </o>
Am I to also add Dir for the red highlighted txt?<o></o>
<o> </o>
For example would the following code be correct using the following paths?<o></o>
<o> </o>
1 "c:\local\myfolder\" (this is the location where the template is held, so I change the path to reflect the folder holing my template<o></o>
2 "template.xls" (I need to change the name to reflect the name of my template).<o></o>
<o> </o>
3 I need to tell the macro where to store the new folder ?<o></o>
<o> </o>
If using the folowwing path c:\local\trial\<o></o>
<o> </o>
Would I need to add the following<o></o>
<o> </o>
Fldr = "c:\local\trial\"<o></o>
<o> </o>
If I need to change the following- .copyfile fldr & tmp, fldr & fname & "\" & fname & ".xls" - I have no idea on what to write<o></o>

Any assistance appreciated <o></o>

 

[Weaver]

If it's not possible for you to store the template in the destination folder of the new files (how the code is currently configured) then create another variable called fldr1 and name that for the location of the new files. Then the copy code becomes

For Each fname In listrange.Cells<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
  MkDir fldr1 & fname<o:p></o:p>
  .copyfile fldr & tmp, fldr1 & fname & "\" & fname & ".xls"<o:p></o:p>
Next fname<o:p></o:p><o:p></o:p>

In this case, fldr will be the path to the template file.