title: Why does Excel inconsistently manage the cut rows range during a cut and paste to a new sheet?
body: <p>I have identified an inconsistency in the source range address for cut cells.</p>
  <p>I have a utility in an Excel-based tool that migrates rows that the user selects. The user then must select the destination, which can be on the same sheet or on a different sheet.</p>
  <p>The issue of Excel leaving blank lines when cutting and inserting into a different sheet are well known (c.f. <a href="https://stackoverflow.com/questions/24131874/cutting-row-with-data-and-moving-to-different-sheet-vba">Cutting Row with Data and moving to different sheet VBA</a> and <a href="https://stackoverflow.com/questions/38440947/excel-vba-remove-blank-row-after-cut">Excel VBA remove blank row after cut</a>) and they have solutions for addressing it. However, my tool uses a different approach (similar to <a href="https://stackoverflow.com/a/27093382/9101981">https://stackoverflow.com/a/27093382/9101981</a>) - which I will now show does not work as intended.</p>
  <pre><code>Sub TestMigration()
  ''    Source Cells = migrateSource.Range(&quot;A3:A4&quot;) ' this simulates how the ranges are selected in the real world application
  ''    Target Cells = migrateTarget.Range(&quot;A7&quot;) ' this simulates the real world application where this is chosen through a pick box
      MigrateRows migrateSource.Range(&quot;A3:A4&quot;), migrateTarget.Range(&quot;A7&quot;)
  End Sub
  
  Sub MigrateRows(sourceCells As Range, targetCells As Range)
                  Debug.Print &quot;Original source address: &quot; &amp; sourceCells.Worksheet.Name &amp; &quot;,&quot; &amp; sourceCells.Address
      sourceCells.EntireRow.Cut ' the command used in the real world application
                  Debug.Print &quot;After cut source address: &quot; &amp; sourceCells.Worksheet.Name &amp; &quot;,&quot; &amp; sourceCells.Address
      targetCells.Insert
                  Debug.Print &quot;After insert source address: &quot; &amp; sourceCells.Worksheet.Name &amp; &quot;,&quot; &amp; sourceCells.Address
      If Not (sourceCells.Worksheet Is targetCells.Worksheet) Then sourceCells.EntireRow.Delete
  End Sub
  </code></pre>
  <p>The source and target data is:</p>
  <p><a href="https://i.stack.imgur.com/s7zvm.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/s7zvm.png" alt="Source data" /></a><a href="https://i.stack.imgur.com/bNEN5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bNEN5.png" alt="Target data" /></a></p>
  <p>The expected results are:</p>
  <p><a href="https://i.stack.imgur.com/p2xe2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/p2xe2.png" alt="Expected source data after cut and insert" /></a><a href="https://i.stack.imgur.com/IquOo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/IquOo.png" alt="Expected target data after cut and insert" /></a></p>
  <blockquote>
  <p>Original source address: MigrateSource,$A$3:$A$4</p>
  <p>After cut source address: MigrateSource,$A$3:$A$4</p>
  <p>After insert source address: MigrateSource,$A$3:$A$4</p>
  </blockquote>
  <p>The actual results are:</p>
  <p><a href="https://i.stack.imgur.com/eN0Wu.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/eN0Wu.png" alt="enter image description here" /></a><a href="https://i.stack.imgur.com/5Y4FV.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5Y4FV.png" alt="enter image description here" /></a></p>
  <blockquote>
  <p>Original source address: MigrateSource,$A$3:$A$4</p>
  <p>After cut source address: MigrateSource,$A$3:$A$4</p>
  <p>After insert source address: MigrateSource,$A$7:$A$8</p>
  </blockquote>
  <p>The data above shows that the <strong>source</strong> range has taken on address of the inserted cells, but <strong>not</strong> the worksheet name. If the cut cells address were to &quot;move&quot;, I would expect it to also take on the new worksheet.</p>
  <h2>Additional information - what else did I try doing this?</h2>
  <ol>
  <li>I tried to create a copy of the source cells. As this is copying a reference, I did not expect it to work, and was not disappointed.</li>
  </ol>
  <p>Code:</p>
  <pre><code>Sub MigrateRows1(sourceCells As Range, targetCells As Range)
  Dim tSourceRowsCopy As Range
      Set tSourceRowsCopy = sourceCells
              Debug.Print &quot;Original source address: &quot; &amp; sourceCells.Worksheet.Name &amp; &quot;,&quot; &amp; sourceCells.Address
      sourceCells.EntireRow.Cut ' the command used in the real world application
              Debug.Print &quot;After cut source address: &quot; &amp; sourceCells.Worksheet.Name &amp; &quot;,&quot; &amp; sourceCells.Address
              Debug.Print &quot;After cut source_copy address: &quot; &amp; tSourceRowsCopy.Worksheet.Name &amp; &quot;,&quot; &amp; tSourceRowsCopy.Address
      targetCells.Insert
              Debug.Print &quot;After insert source address: &quot; &amp; sourceCells.Worksheet.Name &amp; &quot;,&quot; &amp; sourceCells.Address
              Debug.Print &quot;After insert source_copy address: &quot; &amp; tSourceRowsCopy.Worksheet.Name &amp; &quot;,&quot; &amp; tSourceRowsCopy.Address
      If Not (sourceCells.Worksheet Is targetCells.Worksheet) Then tSourceRowsCopy.EntireRow.Delete
              'Debug.Print &quot;After delete source address: &quot; &amp; tSourceRows.Worksheet.Name &amp; &quot;,&quot; &amp; tSourceRows.Address
              'Debug.Print &quot;After delete source_copy address: &quot; &amp; tSourceRowsCopy.Worksheet.Name &amp; &quot;,&quot; &amp; tSourceRowsCopy.Address
  End Sub
  </code></pre>
  <blockquote>
  <p><strong>Test Run 1 (MigrateRows1)</strong></p>
  <p>Original source address: MigrateSource,$A$3:$A$4</p>
  <p>After cut source address: MigrateSource,$A$3:$A$4</p>
  <p>After cut source_copy address: MigrateSource,$A$3:$A$4</p>
  <p>After insert source address: MigrateSource,$A$7:$A$8</p>
  <p>After insert source_copy address: MigrateSource,$A$7:$A$8</p>
  <p><em>Outcome: No change</em></p>
  </blockquote>
  <ol start="2">
  <li>I tried to create a new reference based on the address, but created this reference before the move.</li>
  </ol>
  <p>Code:</p>
  <pre><code>Sub MigrateRows2(sourceCells As Range, targetCells As Range)
  Dim tSourceRowsCopy As Range
  Dim tSourceAddress As String
      tSourceAddress = sourceCells.Address
      Set tSourceRowsCopy = sourceCells.Worksheet.Range(tSourceAddress)
              Debug.Print &quot;Original source address: &quot; &amp; sourceCells.Worksheet.Name &amp; &quot;,&quot; &amp; sourceCells.Address
      sourceCells.EntireRow.Cut ' the command used in the real world application
              Debug.Print &quot;After cut source address: &quot; &amp; sourceCells.Worksheet.Name &amp; &quot;,&quot; &amp; sourceCells.Address
              Debug.Print &quot;After cut source_copy address: &quot; &amp; tSourceRowsCopy.Worksheet.Name &amp; &quot;,&quot; &amp; tSourceRowsCopy.Address
      targetCells.Insert
              Debug.Print &quot;After insert source address: &quot; &amp; sourceCells.Worksheet.Name &amp; &quot;,&quot; &amp; sourceCells.Address
              Debug.Print &quot;After insert source_copy address: &quot; &amp; tSourceRowsCopy.Worksheet.Name &amp; &quot;,&quot; &amp; tSourceRowsCopy.Address
      If Not (sourceCells.Worksheet Is targetCells.Worksheet) Then tSourceRowsCopy.Delete
          '    Debug.Print &quot;After delete source address: &quot; &amp; tSourceRows.Worksheet.Name &amp; &quot;,&quot; &amp; tSourceRows.Address
          '    Debug.Print &quot;After delete source_copy address: &quot; &amp; tSourceRowsCopy.Worksheet.Name &amp; &quot;,&quot; &amp; tSourceRowsCopy.Address
  End Sub
  </code></pre>
  <blockquote>
  <p><strong>Test Run 2 (MigrateRows2)</strong></p>
  <p>Original source address: MigrateSource,$A$3:$A$4</p>
  <p>After cut source address: MigrateSource,$A$3:$A$4</p>
  <p>After cut source_copy address: MigrateSource,$A$3:$A$4</p>
  <p>After insert source address: MigrateSource,$A$7:$A$8</p>
  <p>After insert source_copy address: MigrateSource,$A$7:$A$8</p>
  <p><em>Outcome: No change</em></p>
  </blockquote>
  <ol start="3">
  <li>I tried saving the address, and then creating a new reference just before the delete. This worked.</li>
  </ol>
  <p>Code:</p>
  <pre><code>Sub MigrateRows3(sourceCells As Range, targetCells As Range)
  Dim tSourceAddress As String
      tSourceAddress = sourceCells.Address
              Debug.Print &quot;Original source address: &quot; &amp; sourceCells.Worksheet.Name &amp; &quot;,&quot; &amp; sourceCells.Address
      sourceCells.EntireRow.Cut ' the command used in the real world application
              Debug.Print &quot;After cut source address: &quot; &amp; sourceCells.Worksheet.Name &amp; &quot;,&quot; &amp; sourceCells.Address
      targetCells.Insert
              Debug.Print &quot;After insert source address: &quot; &amp; sourceCells.Worksheet.Name &amp; &quot;,&quot; &amp; sourceCells.Address
              Debug.Print &quot;Address to be deleted: &quot; &amp; sourceCells.Worksheet.Name &amp; &quot;,&quot; &amp; tSourceAddress
      If Not (sourceCells.Worksheet Is targetCells.Worksheet) Then sourceCells.Worksheet.Range(tSourceAddress).Delete
              'Debug.Print &quot;After delete source address: &quot; &amp; tSourceRows.Worksheet.Name &amp; &quot;,&quot; &amp; tSourceRows.Address
  End Sub
  </code></pre>