View Single Post
  #23   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' ?

FWIW:

Here's another approach I use with a task-specific project that has multiple
workbooks it works with. It can transfer data between various workbooks
according to a table stored in ThisProject.xla:

Sub XferRangeValues()
' Transfers range data between multiple workbooks;
' Range refs are stored in a dynamic named range on "Xfers" sheet;
' Opens/closes workbooks as needed.
'
Dim vXfers, wksSrc As Worksheet, wksTgt As Worksheet
Dim n&, k, v1, v2

vXfers = ThisWorkbook.Sheets("Xfers").Range("XferRefs")
Const sUsrDat$ = ThisWorkbook.Path & "\UserData\"

For n = LBound(vXfers) To UBound(vXfers)
On Error Resume Next
GetSrc:
Set wksSrc = Workbooks(vXfers(n, 1)).Sheets(vXfers(n, 2))
If wksSrc Is Nothing Then '//file not open
Workbooks.Open sUsrDat & vXfers(n, 1): GoTo GetSrc
End If
GetTgt:
Set wksTgt = Workbooks(vXfers(n, 4)).Sheets(vXfers(n, 5))
If wksTgt Is Nothing Then
Workbooks.Open sUsrDat & vXfers(n, 4): GoTo GetTgt
End If
Err.Clear: On Error GoTo Cleanup

v1 = Split(vXfers(n, 3), ","): v2 = Split(vXfers(n, 6), ",')
For k = LBound(v1) To UBound(v1)
wksTgt.Range(v2(k)) = Application.Transpose(wksSrc.Range(v1(k)))
Next 'k
wksSrc.Parent.Close True: wksTgt.Parent.Close True
Next 'n

Cleanup:
Set wksSrc = Nothing: Set wksTgt = Nothing
End Sub 'XferRangeValues

--
Garry

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