VBA help required: Creating multiple workbooks from a template and a list of names

[CarolynL]

Hopefully someone smarter than me can provide me with a neat VBA macro that will do the following;

The easiest way I can describe the scenario I am trying to create, is to use a company list of personnel (my index) and to generate a time-sheet workbook per person based on a pre-populated template. This time-sheet is saved as the persons name and has the persons name entered into cell D:10

I have a workbook which contains two worksheets;
1) An Index sheet which contains a list of names that I wish to use in Q16 downwards (note the length of this list will vary each time I run this)
2) A "template" sheet which I wish to duplicate in new workbooks
3) A second "data" sheet that I wish to copy across in new workbooks


I need a macro that will take the "template" and "data" sheets and copy it into a new workbook, renaming each new workbook to each name in my Index sheet. I also want that same Name to be copied into cell reference D:10 of the "template" each time.

The end result is that I should have a series of new files generated and saved which are named the same as the Index list, with both the "Template" sheet and the "Data" sheet present, with the cell D:10 pre-populated with the Name provided in the "Template" sheet.

For ease, I'll save these into My Documents for now. (If you need that info?)

I'm using Excel 2010, and have some knowledge of macros through using these forums for help, however I'm still a newbie so this one is making my head spin!

I hope that makes sense.

 

[JLGWhiz]

Thanks everyone for you above help, but I'm running into a problem with the above code when I place it into my "Personal.xlsb" (it works perfect when ran from the macro in the "This Workbook" module). When I run the code from my personal workbook, since I want to run it on reports I receive, it is saving the new workbooks to where the "Personal.xlsb is located, so I changed it as follows with the intent of the new workbooks to b

I suggest you start a new thread. Piggy back on an old thread is not a good way to get the best results from responders who volunteer their time and effort on the forum. But when you start a new thread, more people will see the post and the odds are better at getting a good solution.

 

[mradomir]

Try it this way.

Sub create()
Dim wb As Workbook, sh1 As Worksheet, lr As Long, rng As Range
Set sh1 = Sheets("Index") 'Edit sheet name
Set sh2 = Sheets("Data") 'Edit sheet name
lr = sh1.Cells(Rows.Count, "Q").End(xlUp).Row
Set rng = sh1.Range("Q16:Q" & lr)
    For Each c In rng
        Sheets("Template").Copy 'Edit sheet name
        Set wb = ActiveWorkbook
        wb.Sheets(1).Range("D10") = c.Value
        sh2.Copy After:=wb.Sheets(1)
        wb.SaveAs c.Value & ".xlsx"
        wb.Close False
    Next
End Sub

I know that is very old thread, but it is a very useful for me, and thank you for that. I have additional question: I have to import additional cell from sheet "data" (for example from column R of the same row) in some other place of sheet "template" (for example in E6 cell). When I try to do this in the same way as you add cells from column Q of sheet "data" to sheet D10 cell in "template", I succeed but I always get cell from the last row, and I need it from the same row. Can you, please, help me?

 

[sheetspread]

I know that is very old thread, but it is a very useful for me, and thank you for that. I have additional question: I have to import additional cell from sheet "data" (for example from column R of the same row) in some other place of sheet "template" (for example in E6 cell). When I try to do this in the same way as you add cells from column Q of sheet "data" to sheet D10 cell in "template", I succeed but I always get cell from the last row, and I need it from the same row. Can you, please, help me?

lr is the last row, change it to whatever you want