Try this code. I open a dialog window so you can select the folder to put
the files. I create a new workbook with one sheet (a copy of your sheet 1).
Then put the filename (D5) into the new workbook, perform a copy -
pastespecial, and then save close the new workbook. The oringal Template
workbook alwaasy stays open but never gets changed.

Sub SaveBooks()

'set sheets to copy
Set OldBk = ThisWorkbook

Set objShell = CreateObject("Shell.Application")
Set fs = CreateObject("Scripting.FileSystemObject")


On Error Resume Next
Set objFolder = objShell.BrowseForFolder(&H0&, "Select Folder ", &H4001&)

If objFolder Is Nothing Then
MsgBox ("Cannot open directory - Exiting Macro")
Exit Sub
End If
On Error GoTo 0

Set oFolderItem = objFolder.Items.Item
Folder = oFolderItem.Path
If Right(Folder, 1) < "\" Then
Folder = Folder & "\"
End If

With OldBk.Sheets("Sheet2")

RowCount = 2
Do While .Range("A" & RowCount) < ""

'get filename
FName = .Range("A" & RowCount)
'Create new workbook with one sheet, template sheet 1
OldBk.Sheets("Sheet1").Copy
Set NewBk = ActiveWorkbook
Set NewSht = NewBk.Sheets("Sheet1")
NewSht.Range("D5") = FName
'change formulas to cells
NewSht.Cells.Copy
NewSht.Cells.PasteSpecial _
Paste:=xlPasteValues

'Save new file
NewBk.SaveAs Filename:=Folder & FName
NewBk.Close savechanges:=False
Loop
End With
End Sub


"Polo78 Lacoste" wrote:



I have a template with two worksheets, sheet1 with the actual template
which has about 20 vlookups and other formulas. Sheet2 contains the
range for the vlookups.

I need help to do a repetitive task of saving one template per file per
name as its own workbook. Sheet2 contains over 300 rows and each row
starting on A2 cell has a name of the file along with the corresponding
vlookup on sheet1 on cell D5.

Basically, the steps I manually do, every month.
1. Open the template above, and on Sheet1, their is a formula on D5
which has
=INDIRECT("Sheet2!$A2") as that is my first row of data.
All other formulas and vlookups depend on the data I changed in
Sheet1!$D5.
2. Copy/Paste values removing all formulas
3. Save it to the name of D5 cell on as a new workbook.
4. I then go back to D5 and change $A2 to $A3 and repeat steps 1 to 3,
etc until I reach end of my rows in Sheet2.

Any hard coding will be greatly appeciated.

Thank you.

Beginner in VBA

*** Sent via Developersdex http://www.developersdex.com ***