Generating new file with multiple sheet template

Ana - Updated on Nov 28, 2021 at 05:37 AM
vcoolio
Posts
1353
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 10, 2022
- Nov 28, 2021 at 05:44 AM
Hello,

I have a file that is currently generating new files based on a template file + a list of names.

The problem is that the file is generating a new file but only for the first sheet of the template and the template file has 4 sheets.

This is what I have:

Sub SaveMasterAs()

 Dim wb As Workbook

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

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

 Set wb = Workbooks.Open(ThisWorkbook.Path & "\template_2021.xlsm")

 For Each c In rNames

 With wb

 .SaveAs Filename:=ThisWorkbook.Path & "\templates\" & c.Value & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

 End With

 Set wb = ActiveWorkbook

 Next c

 wb.Close

End Sub



Can you please guide me in the right direction?

1 reply

vcoolio
Posts
1353
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 10, 2022
250
Updated on Nov 28, 2021 at 05:47 AM
Hello Ana,

I created a mock-up of your "template_2021" file and used your code to create the copies.
It worked well and there weren't any problems. All sheets were copied so I don't know why you are having problems with your code.
Are any error messages arising?

Cheerio,
vcoolio.
1