Need your hep: Creating new workbooks and name it based on a list

[jhong]

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

 

[OssieMac]

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 are:-

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.

 

[Per Jessen]

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.

 

[jhong]

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

 

[OssieMac]

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