How to copy a workbook
in [Programming]
|
From: WLMPilot on 10 May 2010 06:47 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
From: Mike H on 10 May 2010 07:17 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
From: WLMPilot on 10 May 2010 10:43 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
From: JLGWhiz on 10 May 2010 11:02 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" <WLMPilot(a)discussions.microsoft.com> wrote in message news:50A8B57C-B56E-4632-AF52-C41AFAE8B24A(a)microsoft.com... > 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
From: JLGWhiz on 10 May 2010 11:08 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" <WLMPilot(a)discussions.microsoft.com> wrote in message news:50A8B57C-B56E-4632-AF52-C41AFAE8B24A(a)microsoft.com... > 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
|
Next
|
Last
Pages: 1 2 Prev: Can I stop the footer from indenting? Next: Control-Home key behaviour |