Hi Howard,

Am Sat, 15 Feb 2014 11:34:09 -0800 (PST) schrieb L. Howard:

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.

you can't copy not adjacent cells. You can loop through the range to
assign the values to a new sheet. Or you read the cells of the range in
an array:

Sub Test()
Dim myRng As Range
Dim rngC As Range
Dim i As Long
Dim myArr() As Variant

Set myRng = Range("B2,G2,B11,K16,F17")

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

Sheets("Sheet2").Range("A1").Resize(columnsize:=my Rng.Cells.Count) _
= myArr
'Sheets("Sheet2").Range("A1").Resize(rowsize:=myRn g.Cells.Count) _
' =worksheetfunction.Transpose(myArr)
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2