View Single Post
  #22   Report Post  
Posted to microsoft.public.excel.programming
GS[_6_] GS[_6_] is offline
external usenet poster
 
Posts: 1,182
Default Array list writing to an Array of 'scattered cells' ?

your didn't refer correctly. And the ranges have the same direction so
you don't have to transpose.


I put Transpose there to accommodate rows-to-cols OR cols-to-rows when
transferring from a datatable to a summary sheet record row; -it makes no
difference otherwise!

My reusable code...

Sub CopyRangeValues(sSrcTgt$, WksSrc As Worksheet, WksTgt As Worksheet)
' Transfers range data between sheets of open workbook[s].
' Uses Transpose in cases where col data copies to summary row record.
'
' Args: sSrcTgt$ String containing range address pairs.
' (SrcRng|TgtRng,SrcRng|TgtRng...)
' WksSrc The sheet the source data gets copied from.
' WksTgt The sheet the source data gets copied to;
' This sheet can be in any open workbook.
'
Dim n&, v1, v2

v1 = Split(sSrcTgt, ",")
For n = LBound(v1) To UBound(v1)
v2 = Split(v1(n), "|")
WksTgt.Range(v2(1)) = Application.Transpose(WksSrc.Range(v2(0)))
Next 'n
End Sub 'CopyRangeValues

...where the sheet refs include their respective parents (workbooks) like so:

Set wksSrc = ThisWorkbook.Sheets("Data")
Set wksTgt = Workbooks("Some.xls").Sheets("Summary")
sSrcTgt = wksSrc.Range("XferAddrs")

CopyRangeValues sSrcTgt, wksSrc, wksTgt

--
Garry

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