Mass produce workbooks

**glide2131** · 05-06-2013, 08:10 PM

I am looking for a macro to help me mass produce many workbooks that are similar except several cells.

I have a worksheet with a list of numbers in Column A of the first sheet in the workbook (Input). And I have a template on all other pages in the workbook. The way the workbook works is I plug a number in a cell (C4) in the second sheet of the workbook and it fills in other cells of the following worksheets automatically.

So, what I want to do is create a macro that will pull the number from each row in the "Input" sheet and paste it in (Sheet2, C4) then save the workbook in a folder on my C drive (C:\MailMerge) under the name of the number in that row. And then do the same for each row in the Worksheet "Input".
In other words, what I should end up with is basically a mail merge like program but for workbooks instead of single sheets.
Does any one know how to do that?

**marreco** · 05-06-2013, 09:23 PM

Hi.

Read..
http://www.rondebruin.nl/win/section1.htm

**glide2131** · 05-06-2013, 11:59 PM

Thank you, but I do not need to mail merge documents using excel. I need to mail merge or make many copies of an entire workbook.
Thanks

**glide2131** · 05-07-2013, 04:37 PM

I found the following code which is similar to what I am looking for but it does not copy the original workbook:

Sub CreateBooks()

Dim oCell As Excel.Range
Dim oWorkbook As Excel.Workbook

'Added to avoid messages asking to confirm overwriting
' previous existent files with same name

Application.DisplayAlerts = False

For Each oCell In Range("A:A")

If oCell.Value = "" Then Exit For

Set oWorkbook = Workbooks.Add

oWorkbook.Sheets(1).Cells(1, 1).Value = oCell.Offset(0, 1).Value

'If the cell value contains only the file name (instead of the whole path
' the file needs to be saved) it will save into MyDocuments folder
oWorkbook.Close True, oCell.Value

Next oCell

Application.DisplayAlerts = True

End Sub

Can anyone help me?