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

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

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

Show all headers | View raw

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

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