Create Copy of template using macro and Name them as per list

[VJOSHI]

Hi,

Having a tough time creating excel files with names mentioned in a list(150 +) every month. Sure someone has an answer to help me here.

Here is the scenario:

I have a macro enabled workbook "Database" which collates data from several file kept in the source folder and then format the same etc on seperate button clicks.

I just want to create another macro button which can use a standard file "template.xlsx" and create its copies in a specific folder and rename them as per list of employee name mentioned in workbook "Database" sheet : "Team list" column "A:A" .

Adding, I also would like to change sheet name of new file created to match file name and update cell value "C5" as per value mentioned against each name in workbook "Database" sheet : "Team list" column "B:B" but this is just a secondry step.

I wish I could simplyfy my need/question but I m lost..

 

[Norie]

Perhaps something like this, which would go in the 'Database' workbok

Sub NewTeamBooks()
Dim wbNew As Workbook
Dim rngNames As Range
Dim nm As Range
Dim strSaveFolder As String

    strSaveFolder = "C:\FolderToSaveNewWBS\"
    
    With ThisWorkbook.Sheets("Team List")
        Set rngNames = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    End With

    For Each nm In rngNames.Cells

        Set wbNew = Workbooks.Add(Template:="C:\template.xlsx")

        wbNew.Sheets(1).Name = nm.Value
         
        wbNew.Sheets(1).Range("C5").Value = nm.Offset(,1).Value

        wbNew.SaveAs strSaveFolder &  nm.Value & ".xlsx", wbNew.FileFormat

        wbNew.Close False

    Next nm

End Sub

 

[VJOSHI]

Oh Thanks. This works great..

I just have a small problem. the path of destination folder and template keeps changing so I was trying to tweak the code so it can pull path value from cell of another sheet Visible to user "Run Macro" . But not sure what is going wrong .. may be I am changing initial declarations incorrectly.

I have mentioned destination folder path on sheet "Run Macro" cell "I13" and path for template on cell "I11".

Thanks for your prompt response and wish you A Happy New Year..