Copy a list n times

[Jordan22]

Hello

I am totally new in macro's world so i would like to ask a little' help

I have a list in column of A, A2:A?? (so the cell of last data is always different).
I would like to copy that list x times into a new sheet (it would be awesome if i would give a number in a cell to tell how many times should it does)

So in A column (Sheet1)
A1: A
A2: B
A3: C
Ax: PO

And i would like to see this list on a new worksheet x times under each other without empty lines (Sheet2)


Thank you!

 

[onlyadrafter]

Hello,

does this work as expected?

Sub copy()
    MY_NO = InputBox("How many copies?", "SELECT NO OF COPIES")
    Application.ScreenUpdating = False
    For MY_COPIES = 1 To MY_NO
        Sheets("Sheet1").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).copy
        Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
    Next MY_COPIES
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
End Sub

 

[Jordan22]

Oh, i am so greatful! Thank you! It's really awesome!

 

[Jordan22]

Sorry, but i would have an another little question, if you have time.
I would like to start the paste from the 5th line and not from the second, if i try to change the "Offset(1, 0)" to "offset(4,0)" it creates empty lines too between of repeat. Of course i tried to change the "
Range("A")" too, but it wasnt succes.

 

[mehidy1437]

Change A2 to A5.

 

[Jordan22]

Thank you! It's perfect :D Better than my insert some empty rows ideas.

 

[Fluff]

Another option, without loops

Sub Jordan()
   Dim Rng As Range
   Dim Rws As Variant
  
   Rws = InputBox("How many copies do you want")
   If Rws = "" Then Exit Sub
  
   With Sheets("Sheet1")
      Set Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
   End With
   Rng.Copy Sheets("Sheet2").Range("A5").Resize(Rng.Count * Rws)
End Sub

Also with the code from onlyadrafter if you are not on Sheet1 when you run the code, it will fail.