Transfer Data Between Arrays with VBA

The purpose of this article is to show you how to move data from one array to another array and output that data to another place. There are other ways to do this and you can surely fill your boots in this article

Move Array

However, this is how to move data out of Excel, into memory and back into cells with the use of a second array.

The following YouTube tutorial takes you through the process. It is a short video that goes through the process line by line. The following is the file to go with the video.

Arrays.xlsm

The goal of the procedure is not to move the entire array but to move part of the array. In our example we will move columns, 2, 8 and 19 and only these columns will be stored in the second array. In this way filtering out a lot of the unnecessary data in the first array.

The following is the procedure from moving data from the array (ar) to the array (var).

Sub MoveArray()
Dim ar As Variant
Dim var As Variant
Dim arr As Variant
Dim i As Long
Dim j As Long

arr = [{2,8,19}]
ar = Sheet2.[a1].CurrentRegion
ReDim var(1 To UBound(ar), 1 To UBound(arr))

    For i = 1 To UBound(ar)
        For j = 1 To UBound(arr)
            var(i, j) = ar(i, arr(j))
        Next j
    Next i
Sheet3.[a1].Resize(UBound(var), UBound(arr)) = var

End Sub