Maybe I misunderstood the question, but it looks to me like you are trying to
recreate the pivot cache each time you want to refresh the data. If I
understand this correctly, you should not do that, you should simply refresh
the pivot table with code similar to:

ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh

Of course you would need to identify your specific pivot table. Remember
that if more than one pivot table is attached to a pivot cache, then all
tables will be refreshed when you refresh one.

It is a crazy world we live in.