CODEWITHSUNDEEP
excelms-officeexcel-2010vba
eykanal
eykanal

Reputation: 27017

Get source range for a PivotItem

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":

enter image description here

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?

Upvotes: 5

Views: 501

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33145

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.

Upvotes: 1

Related Questions