I recall being able to keep workbooks open if they were next up so I checked
and found I had revised the 'working' project as follows...
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
If Not vXfers(n + 1, 1) = vXfers(n, 1) Then wksSrc.Parent.Close True
If Not vXfers(n + 1, 4) = vXfers(n, 4) Then wksTgt.Parent.Close True
Next 'n
Cleanup:
Set wksSrc = Nothing: Set wksTgt = Nothing
End Sub 'XferRangeValues
...and updated the component file accordingly. (I store frm/bas/cls files in a
"Src" folder for each project. This is where I pulled code for this thread
from!)
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion