Range Memory Overflow Issue

November 26, 2023

Trying to run a simple macro to copy/paste some columns from one worksheet to another. It works fine when I only use a few columns but when I add all the columns I need I get an error. I think my syntax is right but I heard it could be a range/clipboard memory issue.

Sub CopyValuesDiff()
Dim sourceWs1, dstWsDiff1, As Worksheet, dtToday As Date

dtToday = Format(DateTime.Now, "yyyy-MM-dd hh:mm:ss")
Set sourceWs1 = Sheets("Size_1")
Set dstWsDiff1 = Sheets("Diff_1")

sourceWs1.Range("H6:H300, J6:J300, L6:L300, N6:N300, AF6:AF300, AH6:AH300, AJ6:AJ300," & _
"AL6:AL300, BD6:BD300, BF6:BF300, BH6:BH300, BJ6:BJ300, CB6:CB300, CD6:CD300," & _
"CF6:CF300, CH6:CH300, CZ6:CZ300, DB6:DB300, DD6:DD300, DF6:DF300, DX6:DX300," & _
"DZ6:DZ300, EB6:EB300, ED6:ED300, EV6:EV300, EX6:EX300, EZ6:EZ300, FB6:FB300," & _
"FT6:FT300, FV6:FV300, FX6:FX300, FZ6:FZ300").Copy Destination:=dstWsDiff1.Range("B2")

dstWsDiff1.Range("A3").Value = dtToday

Application.CutCopyMode = False

End Sub

>Solution :

Keep columns’ name in an array

    Dim aCol, RowCnt As Long, i As Long
    Const S_ROW = 6
    Const E_ROW = 300
    RowCnt = E_ROW - S_ROW + 1
    aCol = Split("H,J,L,N,AF,AH,AJ,AL,BD,BF,BH,BJ,CB,CD," & _
    "CF,CH,CZ,DB,DD,DF,DX,DZ,EB,ED,EV,EX,EZ,FB,FT,FV,FX,FZ", ",")
    For i = 0 To UBound(aCol)
        dstWsDiff1.Range("B2").Offset(0, i).Resize(RowCnt).Value = _
            sourceWs1.Cells(S_ROW, aCol(i)).Resize(RowCnt).Value
    Next

Get column index with a nested loop

    Dim i As Long, j As Long, iColDst As Long
    Dim iColSrc As Long, RowCnt As Long
    Const S_ROW = 6
    Const E_ROW = 300
    RowCnt = E_ROW - S_ROW + 1
    iColDst = 2
    For i = 1 To 8
        For j = 8 To 14 Step 2
            iColSrc = (i - 1) * 24 + j
            dstWsDiff1.Cells(2, iColDst).Resize(RowCnt).Value = _
                sourceWs1.Cells(S_ROW, iColSrc).Resize(RowCnt).Value
            iColDst = iColDst + 1 
        Next
    Next