title: Excel VBA - Copy from a range of cells and paste in one cell
body: <p>I need to copy content from a range of cells in <code>sheet1</code>, say <code>A1:A10</code>, and paste all the data in a single cell in <code>sheet2</code> in cell <code>A1</code>
  This seems to be a simple use case but I couldn't find any solution so far. Below is what I tried</p>
  <pre><code>' Attempt 1
  Sheets(&quot;shee1&quot;).Select    
  Range(&quot;A1:A10&quot;).Select
  Selection.Copy
  MsgBox Selection.Text ' Error: Type mismatch. If I add Selection.Text to watch I see an array kind of structure with each index having each cell data
  Dim val = Selection.Text
  Sheets(&quot;sheet2&quot;).Select
  Range(&quot;A1&quot;) = val
  
  ' Attempt 2
  Sheets(&quot;sheet1&quot;).Range(&quot;A1:A10&quot;).Copy(Sheets(&quot;sheet2&quot;).Range(&quot;A1&quot;) 'Doesn't copy in single cell A1, instead copies to multiple rows like in source
  </code></pre>