title: VBA to copy all text from Shapes("Textbox 1") and place it in a range
body: <p>I have a "shapes" text box (TextBox 1) that contains a SQL query as seen below.</p>
  
  <pre><code>Select
  *
  From
  Table1
  Union
  Select
  *
  From
  Table2
  </code></pre>
  
  <p>This query can be edited which means the values in the text box can change. If I record a macro where I select the text box and the select all and then paste the text into <code>Range("A1")</code>, it looks like the following.</p>
  
  <pre><code>Sub Test_1()
  
      ActiveSheet.Shapes.Range(Array("TextBox 1")).Select
      Application.CutCopyMode = False
      Range("A1").Select
      ActiveSheet.Paste
  
  End Sub
  </code></pre>
  
  <p>This will output the following</p>
  
  <pre><code>&gt; Row_Column    Text
  &gt;     A1           Select
  &gt;     A2           *
  &gt;     A3          From
  &gt;     A4          Table1
  &gt;     A5          Union
  &gt;     A6          Select
  &gt;     A7          *
  &gt;     A8          From
  &gt;     A9          Table2
  </code></pre>
  
  <p>However, if i change the text to the following.</p>
  
  <pre><code>Select
      *
      From
      Table1
      Union
      Select
      *
      From
      Table2
  Union
  Select
      *
      From
      Table3
  </code></pre>
  
  <p>The macro will not copy the additional lines of code that was added in the text box.</p>
  
  <p>How do you select all from a shapes text box and place the copied text into column A?  I do not want all of the text to be placed in a single cell as i need the text separated as seen in the output example.</p>