Macro to copy and paste a range of values numerous times

[peppa_chami]

Hi All,

I would like to have some help regarding the following macro. I would like to copy a range of values in a certain column and paste it down the same column 365 times, with offsetting one row. For an example;

1) Copy A2:A682 (This will always be the same)
2) Paste it in A684:A1364
3) Then in A1366:A2046

Like wise this needs to be pasted 365 times, always offsetting 1 row.

Could you please help me with a macro for this. Greatly appreciate any help.

 

[Dryver14]

This should do it test on a COPY of your worksheet

Sub CopyDown()

    Dim x As Integer
    Dim i As Integer
    i = 682
    
    
    ActiveSheet.Cells(2, 1).Resize(681, 1).Select
    
    For x = 1 To 365
        i = i + 2
        Selection.Copy Destination:=Cells(i, 1)
        i = i + 680
        
    Next x
    
    


End Sub

 

[JackDanIce]

Alternate suggestion to try:

Sub CopyMacro()

    Dim arr()   As Variant
    Dim x       As Long
    
    arr = range("A2:A683").value
    
    Application.ScreenUpdating = False

    For x = 1 To 365
        Cells(rows.count, 1).End(xlUp).Offset(2).Resize(UBound(arr, 1)).value = arr
    Next x
    
    Application.ScreenUpdating = True
    
    Erase arr

End Sub

 

[peppa_chami]

Works fantastically. Thanks heaps Dryver14. You saved me hours

 

[Dryver14]

JackDanIce,

I am trying very hard to learn arrays as it is definitely faster in almost all cases, I suspect in this instance there will not be a lot in it
as you are also looping 365 times

 

[Dryver14]

Your welcome,

Thanks for the response

 

[JackDanIce]

@Dryver in this situation not a lot, but I too am learning arrays so trying to code with them as much as I can.

I would argue there is some gain (vs yours) from not doing an incremental calculation (i = i + 2) and repeated copy + paste, using the clipboard to repeatedly copy and hold the data.

Agreed though, both suggestions 'write' to the worksheet the same number of times which likely to be the main bottle neck. Alternative is to loop and use the input array to create an output array (ReDim), then 'write' out in single pass.

 

[Dryver14]

A good learn...I only copy once outside the loop though

 

[JackDanIce]

I only copy once outside the loop though

To me it looks like you copy inside the loop:

Sub CopyDown()

    Dim x As Integer
    Dim i As Integer
    i = 682
    
    ActiveSheet.Cells(2, 1).Resize(681, 1).Select
    
    For x = 1 To 365
        i = i + 2
        Selection.Copy Destination:=Cells(i, 1)
        i = i + 680
        
    Next x
    
End Sub

Also, Integers are pretty much a redundant data type in Excel:
https://msdn.microsoft.com/en-us/library/office/aa164506(v=office.10).aspx

 

[Dryver14]

That's interesting, surely this will devastate my 8gig of ram now its not a bit reference any more lol