Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Create WorkBooks from Master File
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Create WorkBooks from Master File
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 JavyD wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining data from 6 workbooks into one master | Excel Discussion (Misc queries) | |||
Auto Create an Excel Backup File on a Daily Basis | Excel Discussion (Misc queries) | |||
how to create a list and link it to the master excel file | Excel Worksheet Functions | |||
How can I create a master spreadsheet from different workbooks | Excel Discussion (Misc queries) | |||
auto create a file | Excel Discussion (Misc queries) |