Hello! I'd like to retrieve the data source criteria that feed any specific cell in a pivot table. This is the data that shows up in a cell formula OUTSIDE the pivot table when you enter a formula referring back to a cell INSIDE the pivot table.

For instance, cell B3 in a pivot table may be showing the Sum of 'Value' for all records in the data source where 'ProductCode' is 'A'. If you got to a cell OUTSIDE this pivot table (for example X5), press '=' and point at B3 and press [ENTER] , '=B3' is NOT the formula that Excel enters in this cell (X5), but rather =GETPIVOTDATA("Value",$A$1,"ProductCode" ,"A").

This is the data I'd like to retrieve in VBA - the fact that the cell I'm selecting in a pivot table is summing up the 'Value' for all records where ProductCode is 'A' (without having to go outside the table and point back at the cell to see it).

I've spent days exploring all the properties of various pivot table objects but cannot find any way to access this data about a cell in a pivot table. I finally actually used a routine with very clunky SENDKEYS commands to actually automate going to a specified field outside the pivot table, entering "=" and pointing back at the cell being inspected to get that cell's criteria in a formula, and then use VBA to retrieve the data out of THAT cell. Very awkward!

It seems crazy that this data is not available directly somehow - obviously the data is 'somewhere in Excel' (otherwise Excel wouldn't have the data to alter formula input when you refer to cells in pivot tables in formulas in cells outside pivot tables), but I couldn't find it anywhere!

The reason I want to retrieve the data is that I've made a procedure that applies an autofilter back on the pivot table's data source so you can see the actual 'live' records that contribute to a pivot cell's value (even for a multiple selection). I know you can see these records in a new worksheet simply by double-clicking on a pivot cell; however, such records are 'dead' records: if you edit them, you're not actually changing the records in the original database. Also, you cannot filter for the records contributing to different pivot table cells by double clicking on a multiple selection (for instance, if you suspect certain kinds of accounting entries are posted to two different accounts arbitrarily, you can't select the two different accounts in the pivot table, double click, and see the records that contribute to BOTH accounts).

Like I said, I did finally get the procedure working; however, it's clunky (for instance, if a user types something while the SEND KEYS routine is running then the procedure could fail), and I'm really surprised that the data I'm looking for about a pivot cell can't be retrieved more elegantly!

Any ideas how the data might be available to VBA without actually inputting keystrokes in cells outside the pivot table?