Auto Create WorkBooks from Master File

[Guest]

Hello guys,

On a weekly basis I create over 200 spreadsheets. Right now I have a code
in place where all I have to do is keep renaming a template by putting a
value in a specific cell, then the code places vlookups in each spreadhsheet
in the folder where the V Look ups feed off that one value. It saves me
hours if not days of work that way. But I still have to create each work
book individually and put in the unique value for each spreadsheet.

Is there away that I can just create a master file with all of the #'s in
column A, and a code and pick up say the value in A1, 1234, open up a
template, plug this 1234 in cell say F40, where the V Look ups would take
care of the rest, then save as the value in A1, then close and save, proceed
to A2 in the master file, on and on, until hits a blank in the master file
then ends the sub?

I'm I kidding myself or what?

Regards,

 

[Dave Peterson]

I put the value in F40 of the first worksheet in the template workbook.

Option Explicit
Sub testme()

Dim curWks As Worksheet
Dim newWks As Worksheet
Dim templateName As String
Dim myCell As Range
Dim myRng As Range

Set curWks = Worksheets("sheet1")
templateName = "C:\my documents\excel\book1.xls"

With curWks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))

For Each myCell In myRng.Cells
Set newWks = Workbooks.Add(template:=templateName).Worksheets(1)
newWks.Range("f40").Value = myCell.Value
Application.Calculate 'just in case
With newWks.Parent
'suppress the "already exists" prompt
Application.DisplayAlerts = False
.SaveAs Filename:=ActiveWorkbook.Path & "\" _
& myCell.Value & ".xls", _
FileFormat:=xlWorkbookNormal
Application.DisplayAlerts = True
.Close savechanges:=False
End With
Next myCell
End With

End Sub