Deleting and reuploading data table without references having #REF issues [Excel]

I created a VBA script that deletes an existing data table (named Data) and replaces it with a new version (that has the same headers and is named Data). I have a lot of formulas and charts that rely on this table and was wondering how I can keep the references working after changing the table (so that pivot tables, formulas, and array formulas update automatically when I update the Data table).

Below are some pictures of the problems I am running into:

Pivot tables not updating with new data

Data turning to #REF! after deleting Data table

Likely what is happening is that once you delete the table (based on your definition of the problem), the link to the chart is then lost.

Rather than delete the table (as a whole), delete the individual data rows:

Sub RemoveTableBodyData()

Dim tbl As ListObject

Set tbl = ActiveSheet.ListObjects("Table1")

'Delete Table's Body Data
  If tbl.ListRows.Count >= 1 Then
    tbl.DataBodyRange.Delete
  End If

End Sub

from: The VBA Guide To ListObject Excel Tables

This should otherwise keep the table reference intact for the chart. FYI: because the data has been updated, you may want to make a call out to refresh any dependent pivots and charts, otherwise it may not be clear the data has updated... I might make the call to refresh once after the data set has been cleared, then again after the update... this will make it clear the pivots/charts have been updated.

Good Luck.