Automatic refresh of pivot tables when data changes

I have a worksheet Data

that web dumps data based on a dynamic link. There is another one PivotTable

with worksheet based pivot tables Data

.

Data

the worksheet uses the following macro and clears the contents of the cells before the web cleans up the new updated data. This data is updated every 1 minute.

I have the following code that will update pivot tables when data is refreshed. ThisWorkbook.Worksheets ("pivot table"). Pivot tables ("PivotTable1"). RefreshTable

Since it takes about 20 seconds to complete the data refresh, there is no data (since the cell contents are cleared first) to refresh the pivot table. So I am getting an error.

Data

the following code is used to update the data:

With ThisWorkbook.Worksheets("Data").QueryTables.Add(Connection:= _
        "<URL redacted>", Destination:=ThisWorkbook.Worksheets("Data").Range("$A$1"))
        .Name = "DataPull"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=True '        .Delete
End With

      

        
        
        
      

    

I've tried updating .RefreshStyle = xlInsertDeleteCells

to .RefreshStyle = xlOverwriteCells

. But it overwrites cells to the end of the new data lines. If the new data (number of lines) is less than the old data lines, then the old data lines at the end are not deleted. I want the data from the last update to persist.

How do I automatically update pivot tables based on the above conditions?