Under the assumption that the macro's in all generated workbooks are the same,
- create a template containing all VBA code (and optionally constant text like headers, footers, print range definitions, etc. - i.e. "everything except data")
- create any new workbook from the template
- insert your data into the WB object
- save as macro enabled worksheet (Excel 2007/2010)
- close it
example
Sub CreateWB()
Dim WB As Workbook
Set WB = Workbooks.Add("MacroTemp.xltm") ' contains VBA, ActiveX, etc.
WB.Worksheets("Sheet1").[A1] = "co-cooo!" ' adding data
WB.SaveAs "MyGenWB", xlOpenXMLWorkbookMacroEnabled
WB.Close
End Sub
In Excel 2007/2010 do not forget to save the template as macro enabled template (*.xltm").
