Hi Garry,

Here is the example/s I'm working off of, includes three from you which I have not been able to figure how to ...Dump the array into the target sheet.

In AbookToLong code the only thing I am trying to do is use the commented out line instead of the resize M2 line to dump to the target workbook.

I failed to note it but I am sure Claus wrote this one.

Seems to me that dumping an array to cells that are not all together in a row or a column is quite difficult.

You were helping me once on this and I was able to persuade the OP to revamp the worksheet to accept the tradional array dump.

I'm back with a need to do dump to 'scattered columns/cells' if practical.

If not, I will pass it on as impractical or not code worthy or whatever.

Howard

Sub AbookToLong()
Dim myRng As Range, MyRng1 As Range
Dim rngS As Range, rngD As Range
Dim i As Long
Dim myArr() As Variant

Dim wksSource As Worksheet, wksTarget As Worksheet
Dim wkbSource As Workbook, wkbTarget As Workbook
Dim rngSource As Range, rngTarget As Range

Set myRng = Range("A2,A4,R20,C10,N2,O4,F8,H12,G14")

Set wkbSource = Workbooks("Array cells to another workbook.xlsm")
Set wkbTarget = Workbooks("Copy of long.xlsm")
Set wksSource = wkbSource.Sheets("Sheet1")
Set wksTarget = wkbTarget.Sheets("Sheet1")

Application.ScreenUpdating = False

For Each rngS In myRng
ReDim Preserve myArr(myRng.Cells.Count - 1)
myArr(i) = rngS
i = i + 1
Next

With wksSource
wksTarget.Range("M2").Resize(columnsize:=myRng.Cel ls.Count) = myArr

'This is the non working attempt by me
'wksTarget.Range("A2,B3,C4,D5,E6,F7,G8,H9,I10") = myArr

End With

'//*****Ignore these commeted out lines******//
'wksSource.Range("C7:C18").Copy
' wksTarget.Range("X2").PasteSpecial Transpose:=True
'wksSource.Range("C33:C50").Copy
' wksTarget.Range("AJ2").PasteSpecial Transpose:=True

Application.ScreenUpdating = False
End Sub


'Another way...
'/ Garry

Sub MoveScatteredValues()
Dim v, vaMyVals(), iIncr%
Const sRngRefs$ = "B2,G2,B11,K16,F17"
For Each v In Split(sRngRefs, ",")
ReDim Preserve vaMyVals(iIncr)
vaMyVals(iIncr) = Range(v).Value
iIncr = iIncr + 1
Next 'v
'Dump the array into the target sheet
'...
End Sub

'-OR-

'..if the range addresses were stored in a named range...

Sub MoveScatteredValues2()
Dim v, vaMyVals(), iIncr%
For Each v In Split(Range("RngRefs").Value, ",")
ReDim Preserve vaMyVals(iIncr)
vaMyVals(iIncr) = Range(v).Value
iIncr = iIncr + 1
Next 'v
'Dump the array into the target sheet
'...
End Sub

'-OR-

'..if the range addresses are not just single cells, then a modified
'version of Claus' idea...

'Range("RngRefs").Value: "B2,G2,B11:F11,K16,F17"

Sub MoveScatteredValues3()
Dim c As Range, sRefs$, vaMyVals(), iIncr%
sRefs = Range("RngRefs").Value
For Each c In Range(sRefs)
ReDim Preserve vaMyVals(iIncr)
vaMyVals(iIncr) = Range(c).Value
iIncr = iIncr + 1
Next 'c
'Dump the array into the target sheet
'...
End Sub