How to copy a workbook

[WLMPilot]

I need to know how to copy a workbook in a macro (Excel 2002)
The filename of the workbook to be copied is "Template" (w/o quotes)
The new workbook will have the name of an employee, ie several workbooks,
each having the name of an employee.

Variable: EMP = "John Doe"
Code needed to copy workbook TEMPLATE and rename to variable EMP.

Thanks,
Les

 

[Mike H]

Hi,

This would go in your 'template' workbook.

This string
S = "aaa,bbb,ccc"

should be changed to your list of employees


Sub Sonic()
Dim V As Variant
Dim S As String

S = "aaa,bbb,ccc"
V = Split(S, ",")
For x = 0 To UBound(V)
ThisWorkbook.SaveAs Filename:=V(x)
Next x
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[WLMPilot]

Thanks. I apologize for not making this clearer. I will have a MASTER
workbook that is used by the boss. Inside the Master workbook will be a
commandbutton to execute a macro that will copy the TEMPLATE workbook and
rename it to match the employee(s) name.

There will be a list of all employees in the Master workbook that I will
read into an array to initially set everything up to match the current
employees. After that, I will have it worked out to copy the TEMPLATE for
each new employee.

Therefore, the code is actually in the MASTER workbook. I wanted to know
what the actual code that will copy the TEMPLATE and rename it (using a
variable that holds the employee's name) will be. I believe I will be able
to add that addition code to read the names.

Thanks,
Les

 

[JLGWhiz]

Hi Les, Since you have the emplyee names in an array, you can use the array
name as the variable to name the workbook.

myNames = Array(Name1, Name2, Name3....Namen)
myPath = ThisWorkbook.Path '<<<assumes same folder as master
For i = LBound(myNames) To UBound(myNames)
ThisWorkbook.SaveAs FileName:=myPath & "\" & myNames(i) & ".xls"
Next

The SaveAS method is creating a copy of the master workbook and naming it
with a name from the array of employee names. This assumes the array of
names is for new employees only. If not, then you would have to create a
separate variable for the new employee name and use that where the
myNames(i) array variable is used above.

 

[JLGWhiz]

Oops! Missed the part about the code being in the Master and copying the
template;

myNames = Array(Name1, Name2, Name3....Namen)
myPath = ThisWorkbook.Path '<<<assumes same folder as master
For i = LBound(myNames) To UBound(myNames)
Workbooks("TEMPLATE.xls").SaveAs FileName:=myPath & "\" & myNames(i) &
".xls"
Next

If TEMPLATE is an object variable for a workbook then just TEMPLATE.SaveAs
etc.

 

[GS]

WLMPilot wrote on 5/10/2010 :

Thanks. I apologize for not making this clearer. I will have a MASTER
workbook that is used by the boss. Inside the Master workbook will be a
commandbutton to execute a macro that will copy the TEMPLATE workbook and
rename it to match the employee(s) name.

There will be a list of all employees in the Master workbook that I will
read into an array to initially set everything up to match the current
employees. After that, I will have it worked out to copy the TEMPLATE for
each new employee.

Therefore, the code is actually in the MASTER workbook. I wanted to know
what the actual code that will copy the TEMPLATE and rename it (using a
variable that holds the employee's name) will be. I believe I will be able
to add that addition code to read the names.

Thanks,
Les

If you're looking to just copy a template file rather than an open
workbook:

'''''''''''''''''''
Dim i As Integer

Const sSourceFile As String = "C:\MyTemplate.xls" 'change to suit
Const STargetPath As String = "C:\MyFolder\" 'change to suit

'Iterate your employees array and assign the names to the copied file
For i = LBound(asMyEmployees) To UBound(asMyEmployees)
FileCopy sSourceFile, sTargetPath & asMyEmployees(i) & ".xls"
Next
'''''''''''''''''''

 

[WLMPilot]

Thanks, that helps me out a lot.

If you could, I would appreciate clarification on a couple of things:
1) What is the purpose of the LBound and UBound?
2) What do you mean by TEMPLATE being an object variable?

I may decide to read the employee name one at a time as I loop through,
possibly a DO WHILE the variable that holds the employee name <> null.

Thanks again,
Les

 

[JLGWhiz]

What is the purpose of the LBound and UBound?

This is a way of setting the parameters of the i varaiable to be used in a
For ... Next loop so that it matches the items in the array. Some arrays
are zero base and others are 1 base, so by using LBound (Lower boundary
value of the array) and UBound (Upper biound value) it automatically matches
whatever base is used. If I knew the array base and how many items are in
the array, I could just have easily used the actual numbers.

What do you mean by TEMPLATE being an object variable?

If you have used this syntax:

Set TEMPLATE = 'some workbook

Then it is a variable for a workbook object, or in VBA speak an Object
Variable.
However, If it is in fact a workbook name, it would be used as:

Workbooks("TEMPLATE.xls") 'or whatever file extension applies.

If it is an object variable, then it can be used without quote marks or file
extension and without the preceding qualification of Workbooks, because the
Object Variable points to all of that in memory. Otherwise, as a workbook
name, it needs all the frills to qualify it so VBA will know what to look
for and where to look.

I may decide to read the employee name one at a time as I loop through,
possibly a DO WHILE the variable that holds the employee name <> null

This For ... Next loop does that:

For i = LBound(myNames) To UBound(myNames)
ThisWorkbook.SaveAs FileName:=myPath & "\" & myNames(i) & ".xls"
Next

I used myNames = Array( ) etc. as an example of creating an array of names.
You can substitute your array name in there in three places and it should
work. To test it, put a MsgBox myNames(i) just before the Next and watch it
change on each loop. Of course, use your array name for the MsgBox, also.

Let me know if it works for you.

 

[WLMPilot]

This has been very helpful. THank you for taking time to explain it to me.

I have the macro written and I am coming up with an error. I have started a
new thread today (5/11/10) with subject Subscript Out of Range Error.

Thanks again,
Les