Create multiple copies of excel based on a single templateSolved/Closed

Question

Hello there,

I need your help.

I need to create multiple excel workbooks using a single template and saving them based on a list of names which is on another excel.

I also want the template to reflect the respective name in one of the cell (say in cell A2).

Can this be done using macro?

Thank you in advance.



System Configuration: Windows / Chrome 89.0.4389.90

TrowaD · Answer

Hi Daher,

Yes.

So you want to loop through a column of names. For each name you want to make copy of your template workbook and rename it to the respective name. Place that respective name in A2 as well.

Do all the workbooks go in the same folder?
Does your template have 1 sheet?

Assuming your template workbook only has 1 sheet, then may I ask why you want to create separate workbooks? It is more convenient to have multiple sheets in a single workbook. 

Best regards,
Trowa

Abudaherabu · Answer

Hi Trowa, 
Let me clarify. 

I have one template with multiple sheets with names at a particular cell in the sheets. It is one workbook per student. It is like a dosier for the student for different periods of the year.

So example:
Name list:
Name     Add.
Aa           juu
Bb           guu
Cc           yuu

Aa.xlsx has one workbook with four sheets same template for all except cell A1 of all four sheets contain Aa in them and address in cell B1.

Appreciate all help.

Regards, 
Daher

Abudaherabu · Answer

Hi Trowa,

Spot on.

That is exactly what I need. 

Is there a macro that you can help me with?

Thank you in advance.

Regards,
Daher

TrowaD · Answer

Hi Daher,

Ok, sweet.

Place the code below in a standard module in the file with the students and their addressess:
Sub RunMe()
Dim sName, sAddress, mPath, mTemplate As String

Application.ScreenUpdating = False

mPath = "C:\Documents\Test Map\"
mTemplate = "Template.xlsx"

For Each cell In Range("A2:A" & Range("A1").End(xlDown).Row)
    sName = cell.Value
    sAddress = cell.Offset(0, 1).Value
    Workbooks.Open (mPath & mTemplate)
    For Each ws In Worksheets
        ws.Select
        Range("A1").Value = sName
        Range("B1").Value = sAddress
    Next ws
    ActiveWorkbook.SaveAs (mPath & sName & ".xlsx")
    ActiveWorkbook.Close savechanges:=xlYes
Next cell

Application.ScreenUpdating = True

MsgBox "Student files have been created.", vbInformation

End Sub

 Change the file path on code line 6 to match yours.
 Change the template file name on code line 7 to match yours.
 Keep in mind that depending on the amount of student workbooks that need to be created, it will take some time to open, edit, save and close all those workbooks. I added a messagebox to let you know when all files have been created.

Best regards,
Trowa

daher · Answer

Hi Trowa,

Thank you for the code.

It works upto line 12. Actually it updated the first name on the template then it fail to save and move on to the next name in the list. There was a run error at line 14, ws.Select.

What does the "ws" do?

Please help. 

Thank you.

Regards,
Abu

daher · Answer

Dear Trowa,

Thanks a million. I found out the issue. 

After I add the variable name and change the selection it works fine. 

Thank you.

Regards,
Daher

Create multiple copies of excel based on a single template

6 responses

Similar discussions

Subscribe To Our Newsletter!