Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109944

Re: Array list writing to an Array of 'scattered cells' ?

Show all headers | View raw

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

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Array list writing to an Array of 'scattered cells' ? "L. Howard" <lhkittle@comcast.net> - 2017-03-13 17:28 -0700
  Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-14 00:24 -0400
    Re: Array list writing to an Array of 'scattered cells' ? "L. Howard" <lhkittle@comcast.net> - 2017-03-13 23:16 -0700
      Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-14 05:38 -0400
        Re: Array list writing to an Array of 'scattered cells' ? "L. Howard" <lhkittle@comcast.net> - 2017-03-14 08:58 -0700
          Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-14 14:25 -0400
            Re: Array list writing to an Array of 'scattered cells' ? "L. Howard" <lhkittle@comcast.net> - 2017-03-14 19:03 -0700
              Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-14 22:09 -0400
                Re: Array list writing to an Array of 'scattered cells' ? "L. Howard" <lhkittle@comcast.net> - 2017-03-24 01:56 -0700
                Re: Array list writing to an Array of 'scattered cells' ? Claus Busch <claus_busch@t-online.de> - 2017-03-24 10:02 +0100
                Re: Array list writing to an Array of 'scattered cells' ? "L. Howard" <lhkittle@comcast.net> - 2017-03-24 08:13 -0700
                Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-24 05:59 -0400
                Re: Array list writing to an Array of 'scattered cells' ? "L. Howard" <lhkittle@comcast.net> - 2017-03-24 08:24 -0700
                Re: Array list writing to an Array of 'scattered cells' ? "L. Howard" <lhkittle@comcast.net> - 2017-03-24 23:07 -0700
                Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-25 03:08 -0400
                Re: Array list writing to an Array of 'scattered cells' ? "L. Howard" <lhkittle@comcast.net> - 2017-03-25 02:16 -0700
                Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-25 05:52 -0400
                Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-25 05:54 -0400
                Re: Array list writing to an Array of 'scattered cells' ? "L. Howard" <lhkittle@comcast.net> - 2017-03-25 07:41 -0700
                Re: Array list writing to an Array of 'scattered cells' ? Claus Busch <claus_busch@t-online.de> - 2017-03-25 16:04 +0100
                Re: Array list writing to an Array of 'scattered cells' ? "L. Howard" <lhkittle@comcast.net> - 2017-03-25 11:05 -0700
                Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-25 18:28 -0400
  Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-25 21:29 -0400
    Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-25 22:41 -0400
    Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-28 12:49 -0400

csiph-web