creatng new files using the names in a range

[SJ-NY]

I need to create 500 files based on a template. The new file names are contained in a range.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I would like to automatically create the new files using the names in the range.
<o> </o>
I have not been able to find code on the site (I know it must be here somewhere)... Can someone please direct me to a posting or provide guidance on how to do this.
<o> </o>
Thank you
Steven

 

[Ruddles]

Re: creatnig new files using the names in a range

It's not too difficult.

What sort of files are they? Excel workbooks?

Where's the template?

What's in the worksheet cell: the full path to the new file like c:\folder\wibble.xls, or just wibble.xls and the path is defined somewhere else (in another cell or in the macro)?

 

[SJ-NY]

Thank you for your help. The file is a 2003 xls file. It is not a template file.
I plan on having a separate file that contains the macro in the same subdirectory as the the files being created. The Range containig the names will be in the same file as the one containing the macro.

 

[Ruddles]

Okay, you mentioned using a template in your first post...

Hopefully this will do the job. Start with a list of names in column A: A1 has the name of the file you want to copy from, and in A2 downwards (with no empty cells!) a list of the files you want created, like this:-

<TABLE style="WIDTH: 102pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=136 border=0><COLGROUP><COL style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1206" width=33><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 25pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=33 height=17></TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" width=103>

A​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>

1​

</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">template.xls</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>

2​

</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">tom.xls</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>

3​

</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">****.xls</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>

4​

</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">harry.xls</TD></TR></TBODY></TABLE>

This will create three copies of template.xls called tom.xls, ****.xls and harry.xls.

The code is:-

[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Sub CopyFiles()[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Dim iLastRow As Long
  Dim iRow As Long
  
  iLastRow = Cells(Rows.Count, 1).End(xlUp).Row
  
  For iRow = 2 To iLastRow
   FileCopy ThisWorkbook.Path & "\" & Range("A1").Value, ThisWorkbook.Path & "\" & Range("A" & iRow).Value
  Next iRow[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]End Sub[/FONT]

 

[Ruddles]

You could insert this before Next iRow to track progress:-

    Range("B" & iRow) = "done"

and/or this after Next iRow to give a total:-

  MsgBox CStr(iLastRow - 1) & " files created from " & Range("A1").Value _
       & " in folder " & ThisWorkbook.Path & Space(10), vbOKOnly + vbInformation

 

[SJ-NY]

This looks great, thank you.