Copy, paste x 12, offset, repeat - how?

[Harshbitterness]

Hello, friends!

Been trying to figure out how to do this. My VBA-fu is quite weak.

I want to create a macro that copies A2, pastes it twelve times, then copies A3 and pastes that directly below. Repeating the process until there are no more values to be copied.

Any suggestions? Thank you very much in advance!

 

[HongRu]

Try.

Sub Repeat12()
    For Each x In Range("A2:A4")
        myText = myText & WorksheetFunction.Rept(x & ",", 12)
    Next x
    
    mySplit = Split(myText, ",")
    
    Range("C2").Resize(UBound(mySplit) + 1) = WorksheetFunction.Transpose(mySplit)
End Sub

 

[Fluff]

You could do that with a formula

=TOCOL(IF(SEQUENCE(,12),TRIMRANGE(A2:A1000)))

 

[Fluff]

Or with a macro

Sub Harshbitterness()
   Dim Ary As Variant
   
   Ary = Evaluate("TOCOL(IF(SEQUENCE(,12),TRIMRANGE(A2:A1000)))")
   Range("C2").Resize(UBound(Ary)).Value = Ary
End Sub