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

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

Hi Howard,

Am Sat, 15 Feb 2014 20:59:24 +0100 schrieb Claus Busch:

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:

or without array directly assigned:

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

i = 1
For Each rngC In myRng
Sheets("Sheet2").Cells(i, 1) = rngC.Value
i = i + 1
Next


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

On Saturday, February 15, 2014 12:39:44 PM UTC-8, Claus Busch wrote:
Hi Howard,



Am Sat, 15 Feb 2014 20:59:24 +0100 schrieb Claus Busch:



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:



or without array directly assigned:



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



i = 1

For Each rngC In myRng

Sheets("Sheet2").Cells(i, 1) = rngC.Value

i = i + 1

Next





Regards

Claus B.

--

Thanks, Claus and Garry, some good options for sure.

I want to go to another workbook and to a single row.
Say "Copy WkBook TO.xlsm" sheet 1, Range("F2:K2").

This is from an excerpt from my cheat sheet to copy from one workbook to another. (everything is properly Dimmed)

If Not IsFileOpen("C:\Users\Howard Kittle\Documents\Copy WkBook TO.xlsm") Then
Workbooks.Open ("C:\Users\Howard Kittle\Documents\Copy WkBook TO.xlsm")
End If

Set wkbSource = Workbooks("Book10.xlsm")
Set wkbTarget = Workbooks("Copy WkBook TO.xlsm")
Set wksSource = wkbSource.Sheets("Sheet1")
Set wksTarget = wkbTarget.Sheets("Sheet1")

With wksSource

wkbTarget.range("F2:K2") = ????

End With

Howard

Well, I got it to work with this.

Thanks Garry and Claus for the help as usual.

Glad I was able to hammer it out myself and I appreciate the code work.

Regards,
Howard

Option Explicit

Sub Test()
Dim myRng As Range
Dim rngC 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("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:=m yRng.Cells.Count) _
= myArr
'Sheets("Sheet2").Range("A1").Resize(rowsize:=myRn g.Cells.Count) _
= WorksheetFunction.Transpose(myArr)

Set wkbSource = Workbooks("Book1.xlsm")
Set wkbTarget = Workbooks("Copy WkBook TO.xlsm")
Set wksSource = wkbSource.Sheets("Sheet1")
Set wksTarget = wkbTarget.Sheets("Sheet1")

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

End Sub

Hi Howard,

Am Sat, 15 Feb 2014 14:56:35 -0800 (PST) schrieb L. Howard:

wksTarget.Range("F1").Resize(columnsize:=myRng.Cel ls.Count) = myArr

if you know the number of items in your array you can also assign the
items to a range
wksTarget.Range("F1").Resize(columnsize:=myRng.Cel ls.Count)
is the same as
wksTarget.Range("F1:J1")
so you can write
wksTarget.Range("F1:J1") = myArr


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

Another way...

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

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion