Get Pivot Table Data returns zero

Try below:

• Data Cache: Enable the option to cache PivotTable data in the connection properties. This way, the PivotTable retains data when opening the consolidated sheet, although the original file remains closed.
• IFERROR Function: Combine the IFERROR function with GETPIVOTDATA to return an alternate value instead of zero when the source is unavailable.

  =IFERROR(GETPIVOTDATA("YourField",PivotTableCell),YourAlternateValue)
  

• Consolidated Data Update: Create a macro to refresh data before saving the consolidated sheet. This will ensure data remains intact upon reopening.
• Non-VBA Approach: Use data validation and user-form inputs to link the data robustly, thereby minimizing direct dependencies.