Repeat a list of names X number of times

[jonpaulson]

I'd like to be able to repeat a list of names X number of times. I've looked around but haven't found anything that fit.

Example: Repeat count = 3

Name1
Name2
Name3
Name4

becomes

Name1
Name2
Name3
Name4
Name1
Name2
Name3
Name4
Name1
Name2
Name3
Name4

[JieJenn]

Here's a simple macro for that. I am not sure how to solve using formula.

Sub CopyAndPaste()
Dim intRepeat As Integer
Dim ws As Worksheet
Dim i As Long

Set ws = ThisWorkbook.ActiveSheet
With ws

intRepeat = .Cells(1, 5).Value

    .Range("D2:D5000").ClearContents
    
    For i = 1 To intRepeat
        .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row).Copy .Range("D" & .Cells(Rows.Count, 4).End(xlUp).Row + 1)
    Next i

End With

End Sub

[Pete_UK]

An easy way to do this, assuming your names are in A1:A4, is to put this formula in A5:

=A1

Then copy this down to A12.

If you want it more automatic, then with the repeat count in B1 you can put this formula in C1:

=IF(ROWS($1:1)>COUNTA(A:A)*$B$1,"",INDEX(A:A,MOD(ROWS($1:1)-1,COUNTA(A:A))+1))

then copy this down as far as you need to (it doesn't matter if you copy it too far - you will just get blanks).

Hope this helps.

Pete

[bebo021999]

Assuming repeated list starts from B1

B1=CHOOSE(IF(MOD(ROW()-ROW($B$1)+1,4)=0,4,MOD(ROW()-ROW($B$1)+1,4)),"Name1","Name2","Name3","Name4")

Drag down.

[jonpaulson]

Thanks everyone. I needed a formula solution so Pete I modified your's a little and it worked well.