Get source range for a PivotItem

Question

A PivotTable allows you to "drill down" into a set of data by double-clicking on a value, or by right-clicking on a field and selecting "show details":

My question is, are there any methods in VBA (Office 2010) that can capture this data? I've looked through the documentation on the PivotItem, PivotField, PivotTable, and PivotCache objects, but I didn't see anything that would return the detail data underneath a data or field cell. Can this be done in a single step? Is there a multiple step process available to do this?

Answer 1

No, is the answer unfortunately. While you have access to the PivotCache object, there is not programmatic access to the data. You can, however, get back to the source data which should match the cache as long as it's been refreshed. I did a series of posts on filtering the source data because I didn't like how Excel created a new sheet when you drill down.

http://www.dailydoseofexcel.com/archives/2010/11/08/filter-pivot-table-source-data/ http://www.dailydoseofexcel.com/archives/2010/11/09/filter-pivot-table-source-data-ii/ http://dailydoseofexcel.com/archives/2010/11/10/filter-pivot-table-source-data-calling-procedures/

Ultimately my experiment failed with grouped data, particularly dates. If you don't have grouped data or that doesn't apply to your situation, you can a Range object using

Application.Evaluate(Application.ConvertFormula(pt.SourceData, xlR1C1, xlA1))

where pt is a PivotTable object.