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.

 

[unklevig]

Sub create ()
'From JLGWhiz on MrExcel
Dim wb As Workbook, sh1 As Worksheet, lr As Long, rng As Range
Set sh1 = Sheets("CAB")
Set sh2 = Sheets ("CAV")
lr = sh1.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = sh1.Range("A5:A" & lr)
For Each C In rng
Sheets("CAL").Copy
Set wb = ActiveWorkbook
wb.Sheets(1).Range("A1") = C.Value
sh2.Copy After:=wb.Sheets(1)
'wb.SaveAs C.Value & ".xlsx" 'Note when I use this code it works fine, but it saves the new workbooks to "Documents"
wb.SaveAs ThisWorkbook.Path & Application.pathseperator & C.Value & ".xlsx" 'Note when I use this code I get the error
wb.Close False
Next
End Sub

Appreciate it!

 

[RoryA]

You should have just copied my code. It's pathseparator, not pathseperator.

 

[unklevig]

You should have just copied my code. It's pathseparator, not pathseperator.

You my friend are a genius, seems to work like a charm! I was trying to do some learning and typing as the codes popup in the VBA module, I still fat-fingered it somehow.

 

[RoryA]

Glad to help.

 

[JLGWhiz]

This also works"

wb.SaveAs ThisWorkbook.Path & "\" & c.Value & ".xlsx"

and eliminates the spelling problem.

 

[RoryA]

As long as you aren't on a Mac.

 

[JLGWhiz]

The only thing I ever liked about a MAC was the graphics, and that comparison was made back in the 1990s, so it may not even hold true anymore. But we all have our preferences and mine is PC.

 

[RoryA]

So is mine as far as Excel is concerned, but I don't always have control over my target audience!

 

[JLGWhiz]

{Quote]but I don't always have control over my target audience![/Quote]
I heard that!

 

[unklevig]

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 be saved in the file location of the workbook I'm running the macro on:

FROM:
wb.SaveAs ThisWorkbook.Path & Application.pathseparator & C.Value & ".xlsx"
TO:
wb.SaveAs ActiveWorkbook.Path & Application.pathseparator & C.Value & ".xlsx"

I'm now getting a Run-time error of "1004: Microsoft Excel cannot access the file 'C:xxxxxx. There are several possible reasons:
The file name or path does not exist
The file is being used by another program
The workbook you are trying to save has the same name as a currently open workbook."

Any help on making the macro work from my "Personal" workbook would be greatly appreciated.