GetPivotData getting #REF error while worksheet is being replaced in VBA

[ninaking]

Hello,

I've created a PivotTable with VBA which works fine. The problem is that when I delete and create a new pivottable on a new worksheet to update the values, all of my formulas on an existing worksheet end up with the #REF error.

I need to reference this PivotTable to do a VLookup of User Name to retrieve the correct values to update another table. So my formula is: =GETPIVOTDATA("Count Of Fruits",User_Name_PivotTable,"Segment","Fruits","User Name",A2)

The User_Name_PivotTable is a named range that refers to the ResultsPivotTable worksheet that's getting deleted and replaced with the new data.

WorksheetName: ResultsPivotTable
User_Name_PivotTable is a defined name which refers to: ResultPivotTable!$A$1

How do I keep my GetPivotData function from filling with errors while I'm creating the new worksheet with the same name? Or is there a better way to do this? I have to do all of it automatically as my users are not excel-proficient at all. Please help!!
[TABLE="width: 500"]
<tbody>[TR]
[TD]User_Name (A1)
[/TD]
[TD]Fruits (B1)
[/TD]
[TD]Vegetables (C1)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]10
[/TD]
[TD]25
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jack
[/TD]
[TD]15
[/TD]
[TD]30
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mole999]

the #ref is because you are deleteing the pivot table, if you can get it to refresh wit the new data then that should no longer be the issue

If you can't then the only way i know is to use vba to rebuild the formuals as part of the routine