View Single Post
  #19   Report Post  
Posted to microsoft.public.excel.programming
L. Howard L. Howard is offline
external usenet poster
 
Posts: 852
Default Array list writing to an Array of 'scattered cells' ?

I got an error at first and added Dim vaSrc.
Now I get no error but no copy to the workbooks.

I changed the extension in the code from .xls to .xlsm for the target workbooks.

Here is the code as I modified it, any suggestions?

Thanks, Howard

Sub Copy_Range_to_Range_Single_to_Single()
Dim n&, v1, v2

Dim wksSrc As Worksheet, wksTgt As Worksheet
Dim vaSrc


Const sSrc$ = "A1,C3,E5,G7,I10": Const sTgt$ = "P2,N4,L6,J8,H10"
Const sSrcTgt$ = "A4:A6|O4:O6,C5:C8|P5:P8,A9|Q9,B11|R11"

'Set ref to source sheet
Set wksSrc = ThisWorkbook.Sheets("Sheet3")

On Error GoTo Cleanup

'Set 1st target sheet and process it
Set wksTgt = Workbooks("Other.xlsm").Sheets("Sheet2")
v1 = Split(sSrc, ","): v2 = Split(sTgt, ",")

For n = LBound(vaSrc) To UBound(vaSrc)

wksTgt.Range(v2(n)) = wksSrc.Range(v1(n))
Next 'n

'Set 2nd target sheet and process it
Set wksTgt = Workbooks("SomeOther.xlsm").Sheets("Sheet4")
v1 = Split(sSrcTgt, ",")

For n = LBound(v1) To UBound(v1)

'Parse the Src:Tgt cell addresses
v2 = Split(v1(n), "|")

Range(v2(1)) = Application.Transpose(Range(v2(0)))
Next 'n

Cleanup:

Set wksSrc = Nothing: Set wksTgt = Nothing
End Sub