title: Paste Array to Range in Excel VBA
body: <p>I am trying to build an array containing ranges using a loop so I can paste it all together to earn some time.
  The loop pastes stock name from column L to another sheet. Then, dynamic values generate in range (CA59:CQ59).
  I tried transpose function just in case, double loop for manual 2d array,and simpler ranges without luck. My best shot was something like</p>
  <pre><code>Sheets(&quot;Stocks | Sort&quot;).Range(&quot;O2:O&quot; &amp; UBound(pool) + 1) = WorksheetFunction.Transpose(pool)   
  </code></pre>
  <p>returned one column. Apologize for my lack of knowledge. Any ideas would be highly appreciated. Here is my code :</p>
  <pre><code>Sub ForecastAll()
  
  Dim line As Range
  Dim pool() As Variant
  Dim i As Long
  
  For i = 2 To 133
      Sheets(&quot;Stocks | Sort&quot;).Range(&quot;L&quot; &amp; i).Copy (Sheets(&quot;Stocks | Synopsis&quot;).Range(&quot;E3&quot;))
      ReDim Preserve pool(i)
      pool(i) = Sheets(&quot;Stocks | Synopsis&quot;).Range(&quot;CA59:CQ59&quot;).Value  
  Next i
  
   Sheets(&quot;Stocks | Sort&quot;).Range(&quot;O2:AE133&quot;).ClearContents
   Sheets(&quot;Stocks | Sort&quot;).Range(&quot;O2:AE133&quot;).Value = pool
  
  End Sub
  </code></pre>
  <p>It returns blanks. Thank you for your time in advance.</p>