Visual Basic Question

Question

Hi!

I have the below code that allows me to have a list of employee names and a template and it will copy the workbook template multiple times and rename based on the names in the list in excel. I'd like to also insert the name from the list into the copied workbook template. I do not know how to do this. There is a line of code within the below that says it can be done - I just don't know enough about VBA to do this. I found this code through You Tube so I was able to copy and paste to utilize the code but that is as far as my VBA skills go. Below is the video from YouTube. Any assistance you can provide would be very much appreciated.

https://youtu.be/IXvRQZw1Gps

Sub SaveMasterAs()

'Excel 10 Tutorial

Dim wb As Workbook

Dim rNames As Range, c As Range, r As Range

'Current file's list of names and ids on sheet1.

Set rNames = Worksheets("Sheet1").Range("A2", Worksheets("Sheet1").Range("A2").End(xlDown))

'Path and name to master workbook to open for copy, saveas.

Set wb = Workbooks.Open(ThisWorkbook.Path & "\PE Template.xlsx")

For Each c In rNames

With wb

'If You Need To Copy Something To the Template Use this line

'.Worksheets("Sheet1").Range("A1").Value = coffset.

'Path and name for copied workbook

.SaveAs Filename:=ThisWorkbook.Path & "\Template Copy\" & c.Value & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

End With

Set wb = ActiveWorkbook

Next c

wb.Close

End Sub

Answer 1

To insert the name from the list into the copied workbook template, you can use the following code:

Sub copy_workbook()

Dim name_list As Range

Dim name_cell As Range

Dim template_book As Workbook

Dim new_book As Workbook

Set name_list = Range("A2:A" & Range("A2").End(xlDown).Row) 'Assuming names start at A2

Set template_book = Workbooks.Open("C:\template.xlsx") 'Change the path to your template workbook

For Each name_cell In name_list

Set new_book = Workbooks.Add(template_book.FullName)

new_book.SaveAs "C:\" & name_cell.Value & ".xlsx" 'Change the path to your desired folder

new_book.Worksheets("Sheet1").Range("A1").Value = name_cell.Value 'Change "Sheet1" to the name of your desired worksheet

new_book.Close SaveChanges:=True

Next name_cell

template_book.Close SaveChanges:=False

End Sub

In this code, we are adding a line that sets the value of cell A1 in the new workbook to the name in the corresponding cell in the name list. This line is:

new_book.Worksheets("Sheet1").Range("A1").Value = name_cell.Value

Make sure to change "Sheet1" to the name of your desired worksheet where you want to insert the name.