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

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" wrote:

> 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

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

"Mike H" wrote:

> 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" wrote:
>
> > 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

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.




"WLMPilot" <(E-Mail Removed)> wrote in message
news:50A8B57C-B56E-4632-AF52-(E-Mail Removed)...
> 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
>
> "Mike H" wrote:
>
>> 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" wrote:
>>
>> > 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

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.


"WLMPilot" <(E-Mail Removed)> wrote in message
news:50A8B57C-B56E-4632-AF52-(E-Mail Removed)...
> 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
>
> "Mike H" wrote:
>
>> 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" wrote:
>>
>> > 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

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
>
> "Mike H" wrote:
>
>> 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" wrote:
>>
>>> 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

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
'''''''''''''''''''
--
Garry

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" wrote:

> 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.
>
>
> "WLMPilot" <(E-Mail Removed)> wrote in message
> news:50A8B57C-B56E-4632-AF52-(E-Mail Removed)...
> > 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
> >
> > "Mike H" wrote:
> >
> >> 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" wrote:
> >>
> >> > 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
>
>
> .
>

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" <(E-Mail Removed)> wrote in message
news:ECAD1FC7-D127-469B-9EF4-(E-Mail Removed)...
> 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" wrote:
>
>> 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.
>>
>>
>> "WLMPilot" <(E-Mail Removed)> wrote in message
>> news:50A8B57C-B56E-4632-AF52-(E-Mail Removed)...
>> > 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
>> >
>> > "Mike H" wrote:
>> >
>> >> 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" wrote:
>> >>
>> >> > 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
>>
>>
>> .
>>

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

"JLGWhiz" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:ECAD1FC7-D127-469B-9EF4-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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.
> >>
> >>
> >> "WLMPilot" <(E-Mail Removed)> wrote in message
> >> news:50A8B57C-B56E-4632-AF52-(E-Mail Removed)...
> >> > 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
> >> >
> >> > "Mike H" wrote:
> >> >
> >> >> 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" wrote:
> >> >>
> >> >> > 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
> >>
> >>
> >> .
> >>
>
>
> .
>