title: Pasting an array of values over a ListObject (Excel table) destroys the Listobject
body: <p>In one of my worksheets, I have a</p>
  
  <pre><code>Private Sub BuggingVba()
  </code></pre>
  
  <p>That <strong>should replace the data in a table with an array of values</strong></p>
  
  <pre><code>    Dim MyTable As ListObject, myData() As Variant
      Set MyTable = Me.ListObjects(1)
      myData = collectMyData ' a function defined somewhere else in my workbook
  </code></pre>
  
  <p>It is probably irrelevant, but before doing so, <strong>I resize</strong> the list object <em>(I expand line by line because if I do it at once, I overwrite what is below my table instead of schifting it.)</em></p>
  
  <pre><code>    Dim current As Integer, required As Integer, saldo As Integer
      current = MyTable.DataBodyRange.Rows.Count
      required = UBound(sourceData, 1) - LBound(sourceData, 1)
      ' current and required are size of the body, excluding the header
  
      saldo = required - current
  
      If required &lt; current Then
          ' reduce size
          Range(DestinBody.Rows(1), DestinBody.Rows(current - required)).Delete xlShiftUp
      Else
          ' expland size
          DestinBody.Rows(1).Copy
          For current = current To required - 1
              DestinBody.Rows(2).Insert xlShiftDown
          Next saldo
      End If
  </code></pre>
  
  <p>If there is any data to insert, <strong>I overwrite the values</strong></p>
  
  <pre><code>    If required Then
          Dim FullTableRange As Range
          Set FullTableRange = MyTable.HeaderRowRange _
              .Resize(1 + required, MyTable.HeaderRowRange.Columns.Count)
          FullTableRange.Value = sourceData
      End If
  </code></pre>
  
  <p><strong>And BAM, my table/ListObject is gone!</strong>  Why does this happen and how can I avoid it?</p>
  
  <pre><code>End Sub
  </code></pre>