Create multiple copies of excel based on a single template [Solved]

Report
-
Posts
2776
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 26, 2021
-
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

6 replies

Posts
2776
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 26, 2021
466
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
Posts
2
Registration date
Wednesday April 21, 2021
Status
Member
Last seen
April 27, 2021

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
Posts
2776
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 26, 2021
466
Hi Daher,

It's still confusing to me.

What do you have now?
One Template workbook with 4 worksheets AND one workbook with a single sheet where the student names are in column A and their addressess in column B?

I think the task is clear though. You want to create a copy of the template workbook for each of the students. Rename the workbook name after the students name and place the students name in A1 and the students address in B1 on each of the four sheets.

Best regards,
Trowa
Posts
2
Registration date
Wednesday April 21, 2021
Status
Member
Last seen
April 27, 2021

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
Posts
2776
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 26, 2021
466
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

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
Posts
2776
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 26, 2021
466
Hi Daher,

"ws" is a chosen name for a variable which represents a worksheet. Code line 13 basically means For each worksheet in all worksheets, but since worksheet is already a used name within VBA, we have to choose a different name, hence ws, short and sweet. It needs to be selected for the sheet to be updated with the name and address in A1 and B1. Then "Next ws" means we move on to the next worksheet within the workbook as long as there are worksheets to move on to.
After that the Template workbook is saved as the student name in the same folder. Then it's closed so we can get to the next name of the workbook with the student names.
Note that the Template workbook is unaltered, since we changed it's name. The Template workbook can then be re-opened on code line 12.

What also is a great help to get a better understanding of what is happening, is by placing your cursor somewhere in the code and hit F8. This will let you go through the code line by line with each press on the F8 button. Just when code line 12 happens and the Template workbook is opened, it will overlap the VBA window, so you will have to select the VBA window on the windows taskbar, for you to continu hitting F8.

I see that I didn't declare the "ws" variable. Maybe that helps. Below code line 2:
Dim ws as worksheet

Let me know if I can help you further. It always annoys me when I post a code that works for me, but doesn't for the requester.

Best regards,
Trowa
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
Posts
2776
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 26, 2021
466
Awesome, that's good to hear!

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!