Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need your hep: Creating new workbooks and name it based on a list
Hi Everyone,
Good Day! Hope you can share some codes with my current problem. I have a template with 5 sheets on it. I update this template once in while, once updated i recreate close to 20 workbooks out of this template with different file name when save. I am thinking, is it possible if i have another "name sheet" on my template with the list of all file names i need to recreate then run a macro to duplicate my workbook template and get the file name on the name sheet tab and the macro will run up to the last cell with name on that name sheet. Hope i explain it clear :) Thanks in Advance. Tops |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need your hep: Creating new workbooks and name it based on a list
Suggestion before doing any work on the code.
Do you think it might be advantageous to have the list of filenames and the macro in a separate workbook. My reasons for this a- 1. The list of file names are probably not required in the new workbooks. 2 It is easy to open another workbook using code and save it as a new file name. 3. You will not have the macro in the new workbooks and if others are using them then they do not have the hassle of approving the macros every time they are opened. (Unless of course you already have other macros in the workbook in which case this suggestion is redundent.) Think about it and post your answer. -- Regards, OssieMac "jhong" wrote: Hi Everyone, Good Day! Hope you can share some codes with my current problem. I have a template with 5 sheets on it. I update this template once in while, once updated i recreate close to 20 workbooks out of this template with different file name when save. I am thinking, is it possible if i have another "name sheet" on my template with the list of all file names i need to recreate then run a macro to duplicate my workbook template and get the file name on the name sheet tab and the macro will run up to the last cell with name on that name sheet. Hope i explain it clear :) Thanks in Advance. Tops |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need your hep: Creating new workbooks and name it based on a list
Hello Tops,
I assume your template is saved as a template (.xlt). Create a new workbook with file names in column a of sheet1 with a heading in row 1. Paste this code to the new workbook. Change MyPath to the location of your template file and change the name of the template file. Sub AddBooks() Dim NewWb As Workbook Set wb = ActiveWorkbook Set FileNameSh = wb.Worksheets("Sheet1") LastRow = Range("A1").End(xlDown).Row MyPath="C:\Temp\" ' Change to suit For r = 2 To LastRow 'Headings in row 1 Set NewWb = Workbooks.Add(MyPath & "MyTemplate.xlt") SaveAsFileName = FileNameSh.Cells(r, "A").Value NewWb.SaveAs Filename:=SaveAsFileName NewWb.Close Set NewWb = Nothing Next End Sub Hopes this helps. --- Per "jhong" skrev i meddelelsen ... Hi Everyone, Good Day! Hope you can share some codes with my current problem. I have a template with 5 sheets on it. I update this template once in while, once updated i recreate close to 20 workbooks out of this template with different file name when save. I am thinking, is it possible if i have another "name sheet" on my template with the list of all file names i need to recreate then run a macro to duplicate my workbook template and get the file name on the name sheet tab and the macro will run up to the last cell with name on that name sheet. Hope i explain it clear :) Thanks in Advance. Tops |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need your hep: Creating new workbooks and name it based on a list
Hi OssieMac,
Thanks for the reply! Well, that a nice idea to have the filenames in a separate workbook! I'll be waiting for your reply, thanks again! Tops On Apr 16, 2:57*pm, OssieMac wrote: Suggestion before doing any work on the code. Do you think it might be advantageous to have the list of filenames and the macro in a separate workbook. My reasons for this a- 1. The list of file names are probably not required in the new workbooks. 2 It is easy to open another workbook using code and save it as a new file name. 3. You will not have the macro in the new workbooks and if others are using them then they do not have the hassle of approving the macros every time they are opened. (Unless of course you already have other macros in the workbook in which case this suggestion is redundent.) Think about it and post your answer. -- Regards, OssieMac "jhong" wrote: Hi Everyone, Good Day! Hope you can share some codes with my current problem. I have a template with 5 sheets on it. I update this template once in while, once updated i recreate close to 20 workbooks out of this template with different file name when save. I am thinking, is it possible if i have another "name sheet" on my template with the list of all file names i need to recreate then run a macro to duplicate my workbook template and get the file name on the name sheet tab and the macro will run up to the last cell with name on that name sheet. Hope i explain it clear :) Thanks in Advance. Tops- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need your hep: Creating new workbooks and name it based on a l
After seeing the reply by Per Jessen I am now wondering if your template is
actually a normal workbook or a template file. If it is a template file then Per's answer is what you will want. However, if it is a normal workbook then try the following. Create a new workbook and copy the following code. Note the comments. I have created a variable for both the original workbook and another for the multiple copies in case you want the copies in a folder of their own. If both in the one folder then just set them both the same. Also don't know what version of Excel you are using so have set macro to run with Excel 97-2003 and have a commented out line of code for Excel 2007 in case you need that. Sub MakeMultiCopies() Dim strTemplatePath As String Dim strSavePath As String Dim rngFileList As Range Dim rngFileName As Range 'Edit following line to match your path for Template file strTemplatePath = "C:\Users\\Documents\Excel\Template\" 'Edit following line to match your path for saving new workbooks strSavePath = "C:\Users\\Documents\Excel\Multi Copies\" With Sheets("Sheet1") Set rngFileList = .Range(.Cells(1, 1), _ .Cells(.Rows.Count, 1).End(xlUp)) End With For Each rngFileName In rngFileList 'Edit following to match your Filename. Workbooks.Open Filename:= _ strTemplatePath & "My Template.xls" 'Use following code to Save in XL97-2003 format ActiveWorkbook.SaveAs Filename:= _ strSavePath & rngFileName.Value & ".xls", _ FileFormat:=xlNormal, CreateBackup:=False 'Substitute following code to save in XL2007 format 'ActiveWorkbook.SaveAs Filename:= _ strSavePath & rngFileName.Value & ".xlsx", _ FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False ActiveWindow.Close Next rngFileName End Sub -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create WorkBooks based List | Excel Programming | |||
Creating a unique list of Cost Codes in Col. A from all worksheets in all workbooks in folder X | Excel Programming | |||
Creating separate workbooks based on filter | Excel Programming | |||
creating a list based criteria | Excel Programming | |||
Creating a List based on your choice from Another List | Excel Discussion (Misc queries) |