Repeating macro without overwriting

[Foxvh]

Hi Guys,

i have a macro that inputs data from one sheet to another and have been trying to get it to repeat based on a cell value everything.
as i want this to create multiple lines every time it loops it just overwrites the first line was input.

Dim lRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
        .Cells(lRow, 6).Value = Worksheets("Input").Range("B5").Value
        .Cells(lRow, 1).Value = Worksheets("Input").Range("C5").Value
        .Cells(lRow, 5).Value = Worksheets("Input").Range("D5").Value
        .Cells(lRow, 4).Value = Worksheets("Input").Range("E9").Value
        .Cells(lRow, 3).Value = Worksheets("Input").Range("E11").Value
        .Cells(lRow, 11).Value = Worksheets("Input").Range("E13").Value
        .Cells(lRow, 8).Value = Worksheets("Input").Range("E15").Value
        .Cells(lRow, 12).Value = Worksheets("Input").Range("E17").Value
        .Cells(lRow, 14).Value = Worksheets("Input").Range("H8").Value
        .Cells(lRow, 7).Value = Worksheets("Input").Range("H10").Value
        .Cells(lRow, 13).Value = Worksheets("Input").Range("H12").Value
        .Cells(lRow, 9).Value = Worksheets("Input").Range("E19").Value
        .Cells(lRow, 10).Value = Worksheets("Input").Range("E20").Value

    End With

    End Sub

 

[Fluff]

Hi & welcome to MrExcel.
What cell shows the number of copies needed?

 

[Foxvh]

Sorry i forgot to send the updated code
the below is the code with the one with the repeater

Sub Insert()


Dim lRow As Long
    Dim ws As Worksheet
    Dim i As Long
    Set ws = Worksheets("Sheet1")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
    For i = 1 To Range("B7").Value
        .Cel ls(lRow, 6).Value = Worksheets("Input").Range("B5").Value
        .Cells(lRow, 1).Value = Worksheets("Input").Range("C5").Value
        .Cells(lRow, 5).Value = Worksheets("Input").Range("D5").Value
        .Cells(lRow, 4).Value = Worksheets("Input").Range("E9").Value
        .Cells(lRow, 3).Value = Worksheets("Input").Range("E11").Value
        .Cells(lRow, 11).Value = Worksheets("Input").Range("E13").Value
        .Cells(lRow, 8).Value = Worksheets("Input").Range("E15").Value
        .Cells(lRow, 12).Value = Worksheets("Input").Range("E17").Value
        .Cells(lRow, 14).Value = Worksheets("Input").Range("H8").Value
        .Cells(lRow, 7).Value = Worksheets("Input").Range("H10").Value
        .Cells(lRow, 13).Value = Worksheets("Input").Range("H12").Value
        .Cells(lRow, 9).Value = Worksheets("Input").Range("E19").Value
        .Cells(lRow, 10).Value = Worksheets("Input").Range("E20").Value
        Next i
    End With

    End Sub

 

[Fluff]

Which sheet is the value on, Sheet1 or Input?

 

[Foxvh]

the value is on input.
sheet 1 is where all the data will be copied to from the input sheet.

 

[Fluff]

Ok, thanks for that.
Rather than looping try

Sub Insert()
   Dim lRow As Long, Rws As Long
   Dim ws As Worksheet
   
   Set ws = Worksheets("Sheet1")
   lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
   
   With Worksheets("Input")
      Rws = .Range("B7").Value
      ws.Cells(lRow, 6).Resize(Rws).Value = .Range("B5").Value
      ws.Cells(lRow, 1).Resize(Rws).Value = .Range("C5").Value
      ws.Cells(lRow, 5).Resize(Rws).Value = .Range("D5").Value
      ws.Cells(lRow, 4).Resize(Rws).Value = .Range("E9").Value
      ws.Cells(lRow, 3).Resize(Rws).Value = .Range("E11").Value
      ws.Cells(lRow, 11).Resize(Rws).Value = .Range("E13").Value
      ws.Cells(lRow, 8).Resize(Rws).Value = .Range("E15").Value
      ws.Cells(lRow, 12).Resize(Rws).Value = .Range("E17").Value
      ws.Cells(lRow, 14).Resize(Rws).Value = .Range("H8").Value
      ws.Cells(lRow, 7).Resize(Rws).Value = .Range("H10").Value
      ws.Cells(lRow, 13).Resize(Rws).Value = .Range("H12").Value
      ws.Cells(lRow, 9).Resize(Rws).Value = .Range("E19").Value
      ws.Cells(lRow, 10).Resize(Rws).Value = .Range("E20").Value
   End With
End Sub

 

[Foxvh]

oh wow that works so much better thank you very much!

I've only just started using VBA but i'm really enjoying working with excel and VBA so trying to learn as much as i can.

 

[Fluff]

You're welcome & thanks for the feedback.