I want to "gather" the values of many (9 or 10 say) scattered cells in one worksheet and copy them to another workbook to a specific row.

All my searches seem to refer to ranges of cells that are all nicely in a column or a row.

I see stuff like this but I cannot find an example where you throw all the scattered cells values in an array and then tell it you want it to go to Workbook XX, Sheet1, Range("B2:K2") and paste the values there.

Dim MyArr(1,0) = 1
Dim MyArr(1,1) = 2
Dim MyArr(1,2) = 3

This first example does a nice job of putting 1 to 10 into A1 to A10.

Sub Sheet_Fill_Array()
Dim myarray As Variant
myarray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
Range("A1:A10").Value = Application.WorksheetFunction.Transpose(myarray)
End Sub

This one I thought would show the values of the cell in myarray one by one in a Msgbox but it errors out.


'/ Wrong number of arguments
Sub From_sheet_make_array()
Dim myarray As Variant
myarray = Range("B2", "G2", "B11", "K16", "F17").Value
'Looping structure to look at array.
For i = 1 To UBound(myarray)
MsgBox myarray(i, 1)
Next
End Sub


Is an array approach the best way to gather all my scattered value and transport then to another workbook?

Thanks.
Howard