How to copy report filter pages data into one worksheet?

[ariannem]

So I've set up a pivot table with filters and have split my reports into separate worksheets under each filter. What I want is to collect all of the data from each filtered worksheet onto one page so that I can see all of the filtered reports in one place. I've been doing this manually, but there are several filters I need to run, and each filter splits into around ten separate sub-reports. Is there any way to set this up using a macro or some other form of coding?

I've tried GETPIVOTDATA, but everytime I switch to a new filter and split new sub-reports from it, the code resets and becomes #REF!

 

[jdellasala]

With a cell within the Pivot Table active, select PivotTable Analyze, select the Options drop down at the far left under the Pivot Table's name, and select Show Report Filter Pages. This should generate a separate worksheet for every item in the filter.
If you just want to use GETPIVOTDATA formulas, make sure it's checked under Options -> Formulas -> Working with formulas -> Use GetPivotData functions for Pivot Table references. It can also be turned On/Off for individual Pivot Tables in the Options under PivotTable Analyze. Note that you can't just copy GetPivotData functions like you would a regular function because they contain hard coded values rather than cell references that would change when copied.