title: Grab certain rows from main array to insert into another array to copy into a target sheet
body: <p>I have a big spreadsheet I parse into other spreadsheets. I have something working, albeit slowly.</p>
  <p>I read that using arrays is a better approach.</p>
  <p>How do I grab certain rows from the main array and insert them into another array to copy into a target sheet at the end?</p>
  <p>Here are the original, working functions:</p>
  <pre class="lang-vb prettyprint-override"><code>Private Function CopyValues(rngSource As Range, rngTarget As Range)
      rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Value = rngSource.Value
  End Function
      
  Private Function RESORT(FROMSHEET As Variant, Column As Variant, TOSHEET As Variant, EXTRA1 As Variant, EXTRA2 As Variant, EXTRA3 As Variant)
      Set i = Sheets(FROMSHEET)
      Set e = Sheets(TOSHEET)
      
      Dim d
      Dim j
      Dim q
      d = 1
      j = 2
      
      e.Select
      Cells.Select
      Selection.Clear
      i.Select
      Rows(1).Copy
      e.Select
      Rows(1).PasteSpecial
      
      Do Until IsEmpty(i.Range(&quot;G&quot; &amp; j))
          If i.Range(Column &amp; j) = &quot;Total&quot; Then
              i.Select
              Rows(j).Copy
              e.Select
              Rows(2).PasteSpecial
              ' CopyValues i.Rows(j), e.Rows(2)
              Exit Do
          End If
          j = j + 1
      Loop
      
      d = 2
      j = 2
      
      Do Until IsEmpty(i.Range(&quot;G&quot; &amp; j))
          
          If i.Range(Column &amp; j) = TOSHEET Or i.Range(Column &amp; j) = EXTRA1 Or i.Range(Column &amp; j) = EXTRA2 Or i.Range(Column &amp; j) = EXTRA3 Then
              d = d + 1
              CopyValues i.Range(i.Cells(j, 1), i.Cells(j, 11)), e.Range(e.Cells(d, 1), e.Cells(d, 11)) 'e.Range(&quot;A&quot; &amp; d)
              
          ElseIf i.Range(&quot;A&quot; &amp; j) = e.Range(&quot;A&quot; &amp; d) And i.Range(&quot;I&quot; &amp; j) = &quot;Total&quot; Then
              d = d + 1
              e.Select
              Rows(2).Copy
              Rows(d).PasteSpecial
              ' CopyValues e.Rows(2), e.Rows(d)
          End If
          j = j + 1
      Loop
      e.Select
      Rows(2).Delete
      Range(&quot;A1&quot;).Select
      
  End Function
  </code></pre>
  <p>Here's what I'm hacking on, many different attempts in there:</p>
  <pre class="lang-vb prettyprint-override"><code>Private Function RESORT2(FROMSHEET As Variant, Column As Variant, TOSHEET As Variant, EXTRA1 As Variant, EXTRA2 As Variant, EXTRA3 As Variant)
      ' Set i = Sheets(FROMSHEET)
      ' Set e = Sheets(TOSHEET)
      Dim d
      Dim j As Long
      Dim i As Long
      Dim k As Long
      
      Dim myarray As Variant
      Dim arrTO As Variant
      
      d = 1
      j = 1
            
      'myarray = Worksheets(FROMSHEET).Range(&quot;a1&quot;).Resize(10, 20)
      myarray = Worksheets(FROMSHEET).Range(&quot;a1:z220&quot;).Value 'Resize(10, 20)
      For i = 1 To UBound(myarray)
          If myarray(i, 9) = TOSHEET Then
              'arrTO = myarray
              '  Worksheets(TOSHEET).Range(&quot;A&quot; &amp; j).Resize(1, 20) = Application.WorksheetFunction.Transpose(myarray(i))
              Worksheets(TOSHEET).Range(&quot;A&quot; &amp; j).Value = Application.WorksheetFunction.Transpose(myarray)
              '   arrTO = j 'Application.WorksheetFunction.Index(myarray, 0, 1)
  
              j = j + 1
                  
          End If
              
      Next
      Worksheets(TOSHEET).Range(&quot;a1&quot;).Resize(10, 20) = arrTO
      
  End Function
  </code></pre>
  <p><strong>First Edit</strong><br />
  I tried cleaning up:</p>
  <pre class="lang-vb prettyprint-override"><code>Private Function RESORT(FROMSHEET As Variant, Column As Variant, TOSHEET As Variant, EXTRA1 As Variant, EXTRA2 As Variant, EXTRA3 As Variant)
      Set FRO = Sheets(FROMSHEET)
      Set TOO = Sheets(TOSHEET)
      
      Dim TOO_IND
      Dim FRO_IND
      Dim TotalRow
      
      TotalRow = 2
      TOO_IND = 2
      FRO_IND = 2
      
      TOO.Cells.Clear
      TOO.Rows(1).Value = FRO.Rows(1).Value
      
      Do Until IsEmpty(FRO.Range(&quot;G&quot; &amp; TotalRow))
          If FRO.Range(Column &amp; TotalRow) = &quot;Total&quot; Then
              FRO.Select
              Rows(TotalRow).Copy
              TOO.Select
              Rows(2).PasteSpecial
              ' CopyValues FRO.Rows(j), TOO.Rows(2)
              Exit Do
          End If
          TotalRow = TotalRow + 1
      Loop
      
      Do Until IsEmpty(FRO.Range(&quot;G&quot; &amp; FRO_IND))
          
          If FRO.Range(Column &amp; FRO_IND) = TOSHEET Or FRO.Range(Column &amp; FRO_IND) = EXTRA1 Or FRO.Range(Column &amp; FRO_IND) = EXTRA2 Or FRO.Range(Column &amp; FRO_IND) = EXTRA3 Then
              TOO_IND = TOO_IND + 1
              TOO.Rows(TOO_IND).Value = FRO.Rows(FRO_IND).Value
          ElseIf FRO.Range(&quot;A&quot; &amp; FRO_IND) = TOO.Range(&quot;A&quot; &amp; TOO_IND) And FRO.Range(&quot;I&quot; &amp; FRO_IND) = &quot;Total&quot; Then
              TOO_IND = TOO_IND + 1
              TOO.Select
              Rows(2).Copy
              Rows(TOO_IND).PasteSpecial
           '   TOO.Rows(TOO_IND).PasteSpecial = FRO.Rows(2).PasteSpecial  ' this isn't working, I need format and formula, if I just do .formula it doesn't work
          End If
          FRO_IND = FRO_IND + 1
      Loop
      
      TOO.Rows(2).Delete
      'Range(&quot;A1&quot;).Select
      
  End Function
  </code></pre>
  <p>It's slower (3.2s vs. 2.86s on my smallest sample set).</p>
  <p>I think the array is going to be the solution. I run this routine multiple times on the same sample set, but with different qualifiers, if in the main I dump the sample set into an array, then pass this array to this sort routine, I think it'll be faster. I'm still do not know how to do my operations on arrays, specifically copying one row from array to array.</p>
  <p><strong>Second Edit</strong><br />
  I'm much closer now! What once took ~133 seconds, now only takes 10.51 seconds!</p>
  <p>I'm still trying to trim up some time. I have not yet coded anything to grab the array once and then pass the array to the RESORT function, I'm looking into that next to see if that will help speed things up.</p>
  <p>Is there a way to copy the formula and the value into the same array?  I don't like the way I do it, but it does work.</p>
  <pre class="lang-vb prettyprint-override"><code>Private Function RESORT(FROMSHEET As Variant, Column As Variant, TOSHEET As Variant, EXTRA1 As Variant, EXTRA2 As Variant, EXTRA3 As Variant)
      Set FRO = Sheets(FROMSHEET)
      Set TOO = Sheets(TOSHEET)
      
      Dim TotalRow
      
      TotalRow = 2
      TOO_IND = 2
      FRO_IND = 2
      
      Dim Col As Long
      Dim FROM_Row As Long
      Dim TO_Row As Long
      
      Const NumCol = 25
      
      Dim myarray As Variant
      Dim myarrayform As Variant
      Dim arrTO(1 To 1000, 1 To 2000)
      Dim arrTotal(1 To 1, 1 To NumCol)
      
      TO_Row = 2
      myarray = Worksheets(FROMSHEET).Range(&quot;a1:z1000&quot;).Value
      myarrayform = Worksheets(FROMSHEET).Range(&quot;a1:z1000&quot;).FormulaR1C1
      
      TOO.Cells.Clear
      
      For Col = 1 To NumCol
          arrTO(1, Col) = myarray(1, Col)
      Next
      
      For FROM_Row = 1 To UBound(myarray)
          If myarray(FROM_Row, Column) = &quot;Total&quot; Then
              For Col = 1 To NumCol
                  arrTotal(1, Col) = myarrayform(FROM_Row, Col)
              Next
              Exit For
          End If
      Next
      
      For FROM_Row = 1 To UBound(myarray)
          If myarray(FROM_Row, Column) = TOSHEET Or myarray(FROM_Row, Column) = EXTRA1 Or myarray(FROM_Row, Column) = EXTRA2 Or myarray(FROM_Row, Column) = EXTRA3 Then
              For Col = 1 To NumCol
                  arrTO(TO_Row, Col) = myarray(FROM_Row, Col)
              Next
              TO_Row = TO_Row + 1
          ElseIf myarray(FROM_Row, 1) = arrTO(TO_Row - 1, 1) And myarray(FROM_Row, Column) = &quot;Total&quot; Then
              For Col = 1 To NumCol
                  arrTO(TO_Row, Col) = arrTotal(1, Col)
              Next
              TO_Row = TO_Row + 1
          End If
      Next
      Worksheets(TOSHEET).Range(&quot;a1&quot;).Resize(1000, 2000) = arrTO
      
  End Function
  </code></pre>