VBA to copy and paste range N times

[jbench18]

Hey, looking for some help with a code that will copy a range of data and paste x numbers of times depending on what value is selected. In addition I would need column A (starting in A8) reference how many times the data set was copied. Thanks in Advance

Drop Down Box: B1

Range of Data: Starting at B8:K8 (Could be 1 row or 1k + plus) so would need offset function

Example:

B1 = 3

Range of Data: B8:K22, copy range and paste starting in row 23. Repeat 1 more time (3 total) bc of value in B1

For Column A:
A8: A22 = 1
A23: A37 = 2
A38 : 52 = 3

 

[DanteAmor]

Try this

Sub copy_paste_range()
    Dim r As Range, i As Long, n As Long
    Set r = Range("B8:K22")
    If Not IsNumeric(Range("B1").Value) Then Exit Sub
    For i = 1 To Range("B1").Value
        n = n + r.Rows.Count
        r.Copy r.Offset(n)
    Next
End Sub

 

[Fluff]

How about

Sub jbench18()
   Dim Usdrws As Long, i As Long
   Usdrws = Range("B" & Rows.Count).End(xlUp).Row
   
   Range("B8:K" & Usdrws).Copy Range("B" & Usdrws + 1).Resize((Usdrws - 7) * (Range("B1") - 1), 10)
   For i = 1 To Range("B1").Value
      Range("A8").Offset((i - 1) * (Usdrws - 7)).Resize(Usdrws - 7).Value = i
   Next i
End Sub

 

[jbench18]

Hey guys, sorry for the late response. @Fluff the code worked perfectly. Thank you very much for your help!

How about

Sub jbench18()
   Dim Usdrws As Long, i As Long
   Usdrws = Range("B" & Rows.Count).End(xlUp).Row
   
   Range("B8:K" & Usdrws).Copy Range("B" & Usdrws + 1).Resize((Usdrws - 7) * (Range("B1") - 1), 10)
   For i = 1 To Range("B1").Value
      Range("A8").Offset((i - 1) * (Usdrws - 7)).Resize(Usdrws - 7).Value = i
   Next i
End Sub

 

[Fluff]

You're welcome & thanks for the feedback

As a matter of interest did Dante's code work as well?