VBA code - to copy a name from a list to a template

[Jo Ann Kaminski]

I have the below VBA code that is working well for my task but I'd like to expand this to include the name from the list into the template that is created. There is line of code that indicates it could be used to make this happen, which I have underlined below - I just don't know how to make it work?

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 & "\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 = c.Offset(, 1).Value 'ID

'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

 

[DanteAmor]

Hi @Jo Ann Kaminski : Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try the following modification:

Sub SaveMasterAs()
  'Excel 10 Tutorial
  Dim wb As Workbook
  Dim rNames As Range, c As Range, r As Range
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  '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 & "\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 = c.Value 'the name from the list
      .Worksheets("Sheet1").Range("B1").Value = c.Offset(, 1).Value 'ID
      '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

I hope to hear from you soon.
Respectfully
Dante Amor
----- --

 

[Jo Ann Kaminski]

Hi,

I'm not sure I understand. I copied your code above and replaced in list Module 1.

The below is a picture of my list in excel.

My apologies I'm not a VBA writer. I'm a VBA duplicator! I can copy and follow along but this has me confused. I'm wanting to go down the list and as a template is saved insert the name from the list into field c4 of the template form.


Thank you for looking at this!

 

[DanteAmor]

field c4 of the template form.

Okay, I understand that you can't modify the code, but what you can do is put the specifications of what you need from the original post.
Putting the name of the list in cell C4 is not something you commented on in your original post, so I couldn't tell where you want it. I put it in cell A1 and in B1 what you call ID.

Now I give you the following modification. In cell A1, as your code originally was, will be the ID and in cell C4 the name.

Please try the macro again:

Sub SaveMasterAs()
  'Excel 10 Tutorial
  Dim wb As Workbook
  Dim rNames As Range, c As Range, r As Range
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  '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 & "\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 = c.Offset(, 1).Value 'ID
      .Worksheets("Sheet1").Range("C4").Value = c.Value 'the name from the list
      '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

Note: Consider that I am only adding this line to your code. If the macro needs any other changes, you should have provided that information in the original post as well.
Please take into account my recommendations for future posts, as that will help you get answers more efficiently.
----- --
I hope to hear from you soon.
Respectfully
Dante Amor
----- --

 

[Jo Ann Kaminski]

I'm now getting the following error on this particuluar line.

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

Run-Time Error 9

Subscript out of range

Thank you for your help. I'm really at a loss with this.

 

[DanteAmor]

Set rNames = Worksheets("Sheet1").

Check that in your workbook you have a sheet with the name "Sheet1"
As I said I did not modify the rest of your macro.
You should test in the same workbook where you had your original macro.

 

[Jo Ann Kaminski]

I'm so sorry to be such a pain but still not working. I'm attaching my list (that contains the macro) and the Template.

Sub SaveMasterAs()
'Excel 10 Tutorial
Dim wb As Workbook
Dim rNames As Range, c As Range, r As Range

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Current file's list of names and ids on sheet1.
Set rNames = Worksheets("Your Summary").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 & "\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 = c.Offset(, 1).Value 'ID
.Worksheets("Your Summary").Range("C4").Value = c.Value 'the name from the list
'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

 

[DanteAmor]

I'm so sorry to be such a pain but still not working

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

you must put the same sheet name.

You said you don't write macros, so why do you modify the code? so we will never end.

I have the below VBA code that is working well for my task

If the macro already worked, why did you change the name of the sheet?

you are changing the name of the sheets in your workbooks and then you change the code but you do not do it correctly but you tell me that it is my code that does not work when you are not having an order and control of changes.

try the following. Assuming you only have one sheet in each book. If it doesn't work, you must tell me the name of each one of your sheets in the two books.

Sub SaveMasterAs()
  'Excel 10 Tutorial
  Dim wb As Workbook
  Dim rNames As Range, c As Range, r As Range
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  'Current file's list of names and ids on sheet1.
  Set rNames = Worksheets(1).Range("A2", Worksheets(1).Range("A2").End(xlDown))
  'Path and name to master workbook to open for copy, saveas.
  Set wb = Workbooks.Open(ThisWorkbook.Path & "\Template.xlsx")
  For Each c In rNames
    With wb
      'If You Need To Copy Something To the Template Use this line
      .Worksheets(1).Range("A1").Value = c.Offset(, 1).Value 'ID
      .Worksheets(1).Range("C4").Value = c.Value 'the name from the list
      '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

 

[Jo Ann Kaminski]

Hi Dante,

Sorry for all the back and forth and the confusion. Originally the VBA code worked to duplicate the template files and put the names on the files from the list so I never assumed that I needed to do anything different going forward. I don't write code so I'm going blind here and it's all trial and error on my part. The Template has 5 visible tabs and two hidden.

I tested the above code with only one tab in the workbook and it works except it give the wrong name on the file - it seems to be off by 1. The file is named for the first person on the list but the field is filled in with the 2nd person from the list. Could this be because of the offset?

Thank you again for looking at this. It is very much appreciated and I'm so sorry to be taking up your time.

 

[DanteAmor]

I'm sorry, although I'd like to, I can't provide further assistance without the proper data. It is difficult to guess the name of your sheets in each book, how many sheets you have in each book, what is the name of each book, and also you modify the macro and make me responsible for the errors of the macro.