Loop to copy multiple cells

New Contributor

Please help, I'm going in circles!

I need to create a macro to populate column E with the values in column K, but it will copy J2# of times.

The data in this file is 10 unique rows that repeat. So E2=K2 and copy it J2 number of times (10). Then E12=K3, and copy that J2 number of times and continue until there are no more values listed in column K. I hope this makes sense.Capture.JPG

1 Reply


Here you go:

Sub CopyData()
    Dim n As Long
    Dim r As Long
    Dim m As Long
    Dim s As Long
    Application.ScreenUpdating = False
    n = Range("J2").Value
    s = 2
    m = Range("K1").End(xlDown).Row
    For r = 2 To m
        Range("E" & s).Resize(n).Value = Range("K" & r).Value
        s = s + n
    Next r
    Application.ScreenUpdating = True
End Sub