Copy a File 'n' Times, New Filenames are "File_n.xls"
 

I have a "master" spreadsheet and a "template" spreadsheet. The master
has various things on it and will be used to aggregate data from
multiple workbooks. One sheet in the master has a list of departments
(say deptA, deptB, deptC...). I have a button on the sheet that I want
to use to generate a copy of the template for each dept in the list
(code below). All files reside in the same folder. I currently have
pieced together working code for various pieces of what I want but
cannot get it to work all together. I am very new at VBA. Your help is
much appreciated!!!

In the code "test.xls" is the template file.

-------
Option Explicit
Private Sub CommandButton1_Click()
Dim myCell As Range
Dim sourceFile, myName
sourceFile = "test.xls"


With Worksheets("Sheet1")
For Each myCell In .Range(("A1"), _
.Cells(.Rows.Count, 1).End(xlUp)).Cells

FileCopy sourceFile, myCell.Value & ".xls"

Next myCell
End With
End Sub