Associate
How do I reference the subtotal range of a pivot table in VBA?
i.e. I want to assign this range as a source for a series collection in a chart
i.e. I want to assign this range as a source for a series collection in a chart
VBA pivot table subtotal range
- Thread starter ArchAnGeL
- Start date
Associate
Well you reference a pivot like so:
Dim pivSheet As Worksheet
Dim myPivot As PivotTable
Set pivSheet = ThisWorkbook.Sheets("sheet4")
Set myPivot = pivSheet.PivotTables("myPivot")
I renamed the pivot table for reference. You can then use the locals view to see what underlying object are available within myPivot, for instance
myPivot.tablerange1.value2 will contain an array holding the values (usually) of your left most pivot column.
You can then loop though this to do stuff with it. Probably not the best way to do it though, you probably want to get a reference to the individual pivot item objects and do it that way. Parsing through internal arrays is very slow in Excel.
I despise pivot tables in all their forms so that is about as much help as I can provide, though you should be able to figure it out from there.
ps you can still actually refer to the cell range in vba if you are dynamically populating a graph on the data you don't have to refer to the pivot as far as I'm aware. Depends how complex your pivot is though, and if your graphs refer to visible data.
Dim pivSheet As Worksheet
Dim myPivot As PivotTable
Set pivSheet = ThisWorkbook.Sheets("sheet4")
Set myPivot = pivSheet.PivotTables("myPivot")
I renamed the pivot table for reference. You can then use the locals view to see what underlying object are available within myPivot, for instance
myPivot.tablerange1.value2 will contain an array holding the values (usually) of your left most pivot column.
You can then loop though this to do stuff with it. Probably not the best way to do it though, you probably want to get a reference to the individual pivot item objects and do it that way. Parsing through internal arrays is very slow in Excel.
I despise pivot tables in all their forms so that is about as much help as I can provide, though you should be able to figure it out from there.
ps you can still actually refer to the cell range in vba if you are dynamically populating a graph on the data you don't have to refer to the pivot as far as I'm aware. Depends how complex your pivot is though, and if your graphs refer to visible data.