Create multiple files from template

[ahmedmohamedkamal]

Hi every one
I have a code edited from old thread that create new excel files based on template and named after specified range in the master sheet
i have a problem that it copy the first cell A1 in every C6 for all generated files
i need A1 copied to C6 1st file and A2 copied to C6 2nd file ...........etc also i need to make the range dynamic according to used cells no just from a1 to a8
The code is

Public Sub SaveTemplate()
  Const strSavePath As String = "C:\My Documents\"
  Const strTemplatePath As String = "C:\My Documents\template.xls"


  Dim rngNames As Excel.Range
  Dim rng As Excel.Range
  Dim wkbTemplate As Excel.Workbook

  Set rngNames = ThisWorkbook.Worksheets("Sheet1").Range("A1:A8").Values

  Set wkbTemplate = Application.Workbooks.Open(strTemplatePath)

  For Each rng In rngNames.Cells
    wkbTemplate.Worksheets("Sheet1").Range("C6").Value = ThisWorkbook.Worksheets("Sheet1").Range("A1:A8").Value
    wkbTemplate.SaveAs strSavePath & rng.Value
  Next rng

  wkbTemplate.Close SaveChanges:=False
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Public Sub SaveTemplate()
Const strSavePath As String = "C:\My Documents\"
Const strTemplatePath As String = "C:\My Documents\template.xls"


Dim rngNames As Excel.Range
Dim rng As Excel.Range
Dim wkbTemplate As Excel.Workbook

With ThisWorkbook.Worksheets("Sheet1")
    Set rngNames = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
End With
Set wkbTemplate = Application.Workbooks.Open(strTemplatePath)

For Each rng In rngNames.Cells
wkbTemplate.Worksheets("Sheet1").Range("C6").Value = rng.Value
wkbTemplate.SaveAs strSavePath & rng.Value
Next rng

wkbTemplate.Close SaveChanges:=False
End Sub

 

[ahmedmohamedkamal]

Thank you very much for your quick reply this work like a charm
I need to copy other cells from mater sheet to generated files (All full cells of column C4 copied to C14 ) and ( Only H2 master sheet copied to L6 copied files )
sorry if this is to much to ask my friend.

Hi & welcome to MrExcel.
How about

Public Sub SaveTemplate()
Const strSavePath As String = "C:\My Documents\"
Const strTemplatePath As String = "C:\My Documents\template.xls"


Dim rngNames As Excel.Range
Dim rng As Excel.Range
Dim wkbTemplate As Excel.Workbook

With ThisWorkbook.Worksheets("Sheet1")
    Set rngNames = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
End With
Set wkbTemplate = Application.Workbooks.Open(strTemplatePath)

For Each rng In rngNames.Cells
wkbTemplate.Worksheets("Sheet1").Range("C6").Value = rng.Value
wkbTemplate.SaveAs strSavePath & rng.Value
Next rng

wkbTemplate.Close SaveChanges:=False
End Sub

 

[Fluff]

How about

Public Sub SaveTemplate()
Const strSavePath As String = "C:\My Documents\"
Const strTemplatePath As String = "C:\My Documents\template.xls"


Dim rngNames As Range, rngC As Range
Dim rng As Range
Dim wkbTemplate As Workbook

With ThisWorkbook.Worksheets("Sheet1")
    Set rngNames = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    Set rngC = .Range("C4", .Range("C" & Rows.Count).End(xlUp))
End With
Set wkbTemplate = Application.Workbooks.Open(strTemplatePath)

For Each rng In rngNames.Cells
    With wkbTemplate.Worksheets("Sheet1")
        .Range("C6").Value = rng.Value
        .Range("C14").Resize(rngC.Count).Value = rngC.Value
        .Range("L6").Value = thisworkbbok.Sheets("Sheet1").Range("H2").Value
    End With

wkbTemplate.SaveAs strSavePath & rng.Value
Next rng

wkbTemplate.Close SaveChanges:=False
End Sub

 

[ahmedmohamedkamal]

How about

Public Sub SaveTemplate()
Const strSavePath As String = "C:\My Documents\"
Const strTemplatePath As String = "C:\My Documents\template.xls"


Dim rngNames As Range, rngC As Range
Dim rng As Range
Dim wkbTemplate As Workbook

With ThisWorkbook.Worksheets("Sheet1")
    Set rngNames = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    Set rngC = .Range("C4", .Range("C" & Rows.Count).End(xlUp))
End With
Set wkbTemplate = Application.Workbooks.Open(strTemplatePath)

For Each rng In rngNames.Cells
    With wkbTemplate.Worksheets("Sheet1")
        .Range("C6").Value = rng.Value
        .Range("C14").Resize(rngC.Count).Value = rngC.Value
        .Range("L6").Value = thisworkbbok.Sheets("Sheet1").Range("H2").Value
    End With

wkbTemplate.SaveAs strSavePath & rng.Value
Next rng

wkbTemplate.Close SaveChanges:=False
End Sub

Perfect
I can't thank you enough

 

[Fluff]

You're welcome & thanks for the feedback

 

[ahmedmohamedkamal]

Sorry man but i don't understand how the loop work i tried many times to edit the code to copy G4 master sheet to F14 1st generated file and G5 to second ......etc
but i cant do this can you help please

 

[Fluff]

Is there anything else that needs to be copied, or is this the final change?

 

[ahmedmohamedkamal]

Is there anything else that needs to be copied, or is this the final change?

This is the final one and sorry for asking a lot

 

[Fluff]

Ok, add this line

    With wkbTemplate.Worksheets("Sheet1")
        .Range("C6").Value = rng.Value
        .Range("F15").Value = rng.Offset(3, 6).Value
        .Range("C14").Resize(rngC.Count).Value = rngC.Value
        .Range("L6").Value = thisworkbbok.Sheets("Sheet1").Range("H2").Value
    End With