Create Multiple Workbooks From A Single Workbook in a predefined template

Please insert data of one record in your template so it's more easy to understand what are fields to move from database to template. Thanks.

Regards,
Antonio

Hi Antonio,

I have modified the template and it shows the formulas I am currently using to generate these files manually and save them as separate files. Material Code in the template is currently selectable from a drop down list.

Please let me know if you need any further info.

Regards,
Veejar

hi GUYS,

ANY IDEAS ON THIS PLZ

Excuse me if I answer late but I have been busy with my work.
See macro 'Macro1' in attached file. Macro create one workbook for each material code and save it in the same path of your workbook with data and template.

Please Login or Register  to view this content.

Regards,
Antonio

Hi Antonio,

Thanks for the help. I do know people are busy and I apologize for being impatient. But I was just trying to get all possible help at the earliest.

Hope you understand

Thanks and Regards,

Veejar

Don't worry, I would like to help you more quickly.

Regards,
Antonio

Hi Antonio,

Just wanted tell you that the code works just fine. Although when run the macro, it gives compatibility warning message between .xls and .xlsx

Is there a way to make sure that the warning message does not appear when you run this macro

Thanks in advance

Regards,

Veejar

Attached file is xlsx for Excel 2007.

Please Login or Register  to view this content.

Regards,
Antonio

Hi Antonio,

The File works just fine.
I cant tell you how grateful I am. Many thanks once again

Regards,
Veejar

Antonio,

If I have to create separate Word documents instead of Excel using the same macro then what changes are required to be done.

Sorry for the trouble.

Thanks in advance,

Regards,

Veejar

Hi Antonio,

Good Morning

If I have to create separate Word documents instead of Excel using the same macro then what changes are required to be done.

Sorry for the trouble.

Thanks in advance,

Regards,

Veejar

In attached zip file there are two files: excel file with data and one template word with tags, i.e. <col_a> that indicates to macro to put there data from column a.
Put both files in the same directory, open excel file and run macro.

Regards,
Antonio

Hi Antonio,

Many thanks for the solutions.

It works just fine. I do have new problem for you to work with for which I am preparing a sample workbook.

Will get back to you in sometime

Regards,

Veejar

Originally Posted by antoka05

Attached file is xlsx for Excel 2007.

Please Login or Register  to view this content.

Regards,
Antonio

Hi Guys,

I am stilling facing a bit of a problem as mentioned in the thread.
I have basically created a macro in my workbook with the following
Code:
Sub Macro1()
Dim shData As Worksheet
Dim shTempl As Worksheet
Dim newWb As Workbook
Dim myPath As String

Dim lastRow As Long, r As Long
Dim matCode As String

Set shData = ThisWorkbook.Sheets("data")
Set shTempl = ThisWorkbook.Sheets("Sheet1")

'get path of this workbook
myPath = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\"))

lastRow = shData.Cells(Rows.Count, "a").End(xlUp).Row

'for each row in data sheet
For r = 2 To lastRow
'copy material code from data sheet to template sheet
shTempl.Range("d6") = shData.Cells(r, "a")
matCode = shTempl.Range("d6")

'copy template sheet in new workbook
shTempl.Copy
Set newWb = ActiveWorkbook

'change active sheet name in new workbook
newWb.ActiveSheet.Name = "Sheet1"

'formula to values
newWb.ActiveSheet.Range("a2:d30").Value = newWb.ActiveSheet.Range("a2:d30").Value

'save and close new workbook
newWb.SaveAs Filename:=myPath & "MMR - " & matCode & ".xlsx", FileFormat:=xlOpenXMLWorkbook, _
CreateBackup:=False
newWb.Close

Next r
Set shData = Nothing
Set shTempl = Nothing
Set newWb = Nothing
End Sub

as you can see that my code is a copy of antonio's code with a few changes. When I run Antonio's Macro it works just fine, but when I run my macro it is not able identify the last row in my data and creates a new workbook named as "MMR -" and shows an error window which just states the number 400.

Just can't figure out what the problem as I am just now learning Excel programming.

Any ideas please let me know

Regards,

Veejar

See attached file, I hope it's what you need.

Regards,
Antonio