Pivot Table Compatibility

To preserve pivot table compatibility, short of writing fairly complicated code, you need to use a 2003 file format, so you can work in both Excel 2007 (Version 12) and 2003 (Version 10). Below are quotes from an MSDN blog explaining why, as well as links to two (2) pages detailing related issues.

Strategies for sharing PivotTables with other users

As noted above, version 12 PivotTables are not downgraded to version 10 PivotTables and will not be refreshable in previous versions of Excel.  If you wish to share PivotTables with people using a previous version of Excel AND they have a need to refresh the PivotTables, you will need to ensure that these PivotTables were created as version 10 PivotTables.

How do I create a version 10 PivotTable in Excel 12?

The simplest way to do this is by using compatibility Mode.  If you start with a new file, save it to the Excel 97-2003 file format and re-open the file, you will enter compatibility Mode.  Any PivotTable that you create while in compatibility mode will be a version 10 PivotTable and will be refreshable when opened in previous versions of Excel.

MS Docs

PivotTable Compatibility

Compatibility mode in the 2007 Office system