When opening a workbook containing a PivotTable which references either an NL(Table) or NL(Rows) report for its data source the following error can be displayed:
Complete text of error message is:
The pivot table 'PivotTableX' could not be refreshed.
The following error was reported: 'Reference is not valid.'.
When a workbook contains a PivotTable that references either an NL(Table) or NL(Rows) report for its data source and the NL(Table) or NL(Rows) report produces a #VALUE then this error will be displayed.
Open the workbook and Run it to produce the error.
Right click on the sheet tabs in the workbook and choose Unhide if it is available.
Unhide all of the hidden sheets (if no hidden sheets exist then move to #4).
Look through each sheet for a #VALUE.
Click on the #VALUE and press Check Error which will reveal and error as to why the report did not refresh properly.
Switch the report into Design Mode and troubleshoot the source of that error.