title: how to retain the clipboard data after changing workbooks in vba?
body: <p>i have a program that copies a range of cells and the needs to paste the contents into a new workbook that is created in code. i can copy the data but somehow the clipboard loses its data whenever i change workbooks to the new one created. i considered copying the cells to an array and then just copying the array to the new workbook but i wont know the size of the array at coding time this varies almost every time the macro runs.
  how do i then keep the data on the clipboard while i change the active workbook?</p>
  
  <pre><code>cell = "k7: l" &amp; row
  Worksheets(1).Range(cell).Select
  
  
  Selection.Copy
  relpath = ThisWorkbook.Path &amp; "\" &amp; "DispersionList.xls"
  
  If Dir(relpath) &lt;&gt; "" Then
     Application.Workbooks.Open (relpath)
     Workbooks("DispersionList.xls").Activate
  Else
      Call createWorkbook
  End If
  
  Worksheets(1).Cells(7, 14).Select
  Selection.PasteSpecial
  
  
  End Sub
  </code></pre>
  
  <p>if i run through the code line by line and check the clipboard it loses its contents at the workbooks.open line</p>