Create Multiple Workbooks With Template At Once

Hi Dak,

Good thing you came to this site, as that is a simple task for Excel VBA!

Paste the following code in the workbook with all the names:
 

Sub RunMe()
Dim oFSO As Object

Set oFSO = CreateObject("Scripting.FileSystemObject")

For Each cell In Range("A2:A5")
    Call oFSO.CopyFile("J:\Kioskea\Test Map\Template.xlsx", "J:\Kioskea\Test Map\" & cell.Value & ".xlsx")
Next cell
End Sub

First let's adjust the code, where you have to replace the path and the file name of the template in the code above (J:\Kioskea\Test Map\Template.xlsx) before the comma on code line 7.
Next change the destiantion path of where all the copied and renamed workbbooks needs to go (J:\Kioskea\Test Map\) after the comma on code line 7.
Also change the range where the file names are located (Range("A2:A5") on code line 6.

To implement the code hit Alt+F11, to open another window, go to the top menu, click on Insert and click on Module. Now paste the code in the big white field. You can close this window now. Back at Excel, hit Alt+F8 and double click RunMe to run the code.

Best regards,
Trowa

Hi Dak,

Feast your eyes on this code:
 

Sub RunMe()
Dim oFSO As Object
Dim MyBook As Workbook
Dim SheetName, HyperName As String

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set MyBook = ActiveWorkbook

With MyBook.Sheets("Sheet1")
    For Each cell In .Range("A2:A5")
        SheetName = .Cells(cell.Row, "B")
        HyperName = .Cells(cell.Row, "C")
        Call oFSO.CopyFile("J:\Kioskea\Test Map\Template.xlsx", "J:\Kioskea\Test Map\" & cell.Value & ".xlsx")
        Workbooks.Open "J:\Kioskea\Test Map\" & cell.Value & ".xlsx"
        ActiveSheet.Name = SheetName
        Range("A1").Value = SheetName
        ActiveSheet.Hyperlinks.Add Anchor:=Range("F1"), Address:=HyperName, TextToDisplay:=HyperName
        Workbooks(cell.Value & ".xlsx").Close savechanges:=True
    Next cell
End With
End Sub

Range A2:A5 is used for the filename.
Range B2:B5 is used for the sheet name and also cell A1.
Range C2:C5 is used for the hyperlinks placed in cell F1.

Glad to be able to help you out! Feel free to ask how stuff works or to add to the code.

Best regards,
Trowa