Search code examples
excelvbacopy-pastevba7

How to make a for loop faster in vba when copying pasting columns

I have a vba script that uses a for loop to copy rows to columns, but this method is very slow when running. Is there a faster way of accomplishing this.

enter image description here

As you can see below, I have data in the 'my data' row and I need each of the rows copied and pasted to the next column to the right. For example, the 1 needs to be copied and pasted all the way to the columns to the right from range(X44:AY44) and so on.

Below is the script that works, but it is too slow for processing.

Sub CopyPasteSV4_SV30()

    Dim r As Range, cell As Range
    Dim i As Integer
    i = 44
    
    For Each cell In Range("X44:X63")
        Range("X" & i).Select
        Selection.Copy
        Range("Y" & i, Range("AY" & i)).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        i = i + 1
    Next cell
End Sub
Solution

  • There is no need to loop, use Select, or use .Copy/.PasteSpecial. Use value transfer.

    Range("Y44:AY63").Value = Range("X44:X63").Value