Best approach to generate copies of a workbook based on the number of rows?

[561414]

Hi. Good day to you.

I would like to populate a "template" worksheet based on an Excel Table like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ClientID[/TD]
[TD]Bank
[/TD]
[TD]Account[/TD]
[TD]Problem[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Big bank[/TD]
[TD]987987987[/TD]
[TD]Didn't pay[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]North bank[/TD]
[TD]654654654[/TD]
[TD]Delayed pay[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]South bank[/TD]
[TD]654654654[/TD]
[TD]Low credit[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Easy bank[/TD]
[TD]852852852[/TD]
[TD]Bankrupt[/TD]
[/TR]
</tbody>[/TABLE]

However, I can only copy 3 of the 4 rows above into the "template" worksheet, so I will need another one to be created. Each 3 records, actually, so if a Client has 7 records, I would need 3 "template" worksheets to be created.

Can you give me a brief example of how to do this?

<code>Private Sub btnCreateForms_Click()
    Dim cell As Range, rng As Range
    Set rng = Sheets("Temp").Range("tbProblems[Bank]")
        For Each cell In rng
            Sheets("Template").Range("D24").Value = cell.Value
            Sheets("Template").Range("L24").Value = cell.Offset(0, 1).Value
            Sheets("Template").Range("D29").Value = cell.Offset(0, 3).Value
            Sheets("Template").Range("D32").Value = cell.Offset(1, 0).Value
            Sheets("Template").Range("L32").Value = cell.Offset(1, 1).Value
            Sheets("Template").Range("D37").Value = cell.Offset(1, 3).Value
            Sheets("Template").Range("D40").Value = cell.Offset(2, 0).Value
            Sheets("Template").Range("L40").Value = cell.Offset(2, 1).Value
            Sheets("Template").Range("D45").Value = cell.Offset(2, 3).Value

        GoTo Nextiteration
        GoTo Nextiteration
Nextiteration:
        Next cell
End Sub</code>

In the example above, I tried to loop from the first record, and copy the two rows below from that position, and skip those rows starting with the fourth to continue again. It only copies the first record.

Please, give me a little push in how to do such thing, any suggestion is welcome.

 

[AlphaFrog]

Try this (Not tested)...

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] btnCreateForms_Click()
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color], rng [color=darkblue]As[/color] Range
    [color=darkblue]Set[/color] rng = Sheets("Temp").Range("tbProblems[Bank]")
    [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] rng.Rows.Count [color=darkblue]Step[/color] 3
        Sheets("Template").Copy After:=Sheets(Sheets.Count)
        [color=darkblue]With[/color] rng.Cells(i, 1)
            Range("D24").Value = .Value
            Range("L24").Value = .Offset(0, 1).Value
            Range("D29").Value = .Offset(0, 3).Value
            Range("D32").Value = .Offset(1, 0).Value
            Range("L32").Value = .Offset(1, 1).Value
            Range("D37").Value = .Offset(1, 3).Value
            Range("D40").Value = .Offset(2, 0).Value
            Range("L40").Value = .Offset(2, 1).Value
            Range("D45").Value = .Offset(2, 3).Value
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]Next[/color] i
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[561414]

Thank you so much, AlphaFrog, I just tested it and I noticed:

1. It does generate the worksheets necessary
2. It's filling some of the data in the worksheet next to the "Temp" worksheet
3. The "Template" worksheets generated haven't received any value

It's definitely a step ahead, though.

 

[561414]

I should have mentioned that the form being populated is where the button is located, sorry about that. Fixed it finally, it looks like this:

Private Sub btnCreateForms_Click()
    Dim i As Long, rng As Range
    Set rng = Sheets("Temp").Range("tbBanks[Bank]")
    For i = 1 To rng.Rows.Count Step 3
        
        With rng.Cells(i, 1)
            Sheets("Template").Range("D24").Value = .Value
            Sheets("Template").Range("L24").Value = .Offset(0, 1).Value
            Sheets("Template").Range("D29").Value = .Offset(0, 3).Value
            Sheets("Template").Range("D32").Value = .Offset(1, 0).Value
            Sheets("Template").Range("L32").Value = .Offset(1, 1).Value
            Sheets("Template").Range("D37").Value = .Offset(1, 3).Value
            Sheets("Template").Range("D40").Value = .Offset(2, 0).Value
            Sheets("Template").Range("L40").Value = .Offset(2, 1).Value
            Sheets("Template").Range("D45").Value = .Offset(2, 3).Value
            Sheets("Template").Copy After:=Sheets(Sheets.Count)
        End With
    Next i
End Sub

Now it's just a matter of cleaning up the Template after this procedure, but that's easy.

Thanks, man, that was really the answer I've been looking for 4 days now!