Insert Template with Loop x Amount of Times

Question

I'm back with another question that probably has a simple answer. I really fall down when it comes to loops, so this is what I am trying to build.

I am building a form where user will type in a number between 1 and 156 (named range "GenNo") and click a Generate button.

I need a loop that will copy a template built in the "Template" tab of the spreadsheet with the named range also being "Template", and then insert it into the main form page the specified amount of times. This way the rest of the content and other named ranges should be pushed down accordingly.

Probably a very simple answer but I am terrible when it comes to loops and would not know where to start.

Thanks for your help.

EDIT: This attempt only generates one template in the form:

Sub Generate()
    ' Check if payslips are already generated
    If Sheets("Data").Range("GenLogic").Value = 1 Then
    MsgBox ("Already Generated! Please clear the form and regenerate.")
    Else
    Sheets("Data").Range("GenLogic").Value = 1
    End If
    ' Loop code
        Do Until Sheets("Data").Range("LoopLogic").Value = Range("GenNo").Value
        Sheets("Template").Range("Template").Copy
        Sheets("Overpayment Form").Range("Start").Insert
        Range("LoopLogic") = Cell.Value + 1
        Loop

End Sub

Worth noting: you inform the user he needs to clear the form and regenerate, but you have no check to ensure this is done before continuing. You may want to add an `exit sub` statement after the first if clause. — CBRF23, Aug 07 '15 at 11:09

score 0 · Accepted Answer · answered Aug 07 '15 at 10:56

i would give this a shot; note that i removed your updating of your loop variables. Also, i've rewritten your loop to use a for, and shift down on insert.

Sub Generate()
' Check if payslips are already generated
If Sheets("Data").Range("GenLogic").Value = 1 Then
MsgBox ("Already Generated! Please clear the form and regenerate.")
Else
Sheets("Data").Range("GenLogic").Value = 1
End If
' Loop code
Dim iFrom As Long, iTo As Long, i As Long
iFrom = Sheets("Data").Range("LoopLogic").Value
iTo = Range("GenNo").Value
For i = iFrom To iTo
    Sheets("Template").Range("Template").Copy
    Sheets("Overpayment Form").Range("Start").Insert Shift:=xlDown
Next


End Sub

Insert Template with Loop x Amount of Times

1 Answers1