Reading pivot tables using vba can be quite easy. Here are a few ways you can do it.
You may also want to read my previous article on formatting pivots.
(Before you dive further, you may want to download this example which will guide you through some of the steps in reading a pivot table data using vba)
Lets look at this peice of code:
For Each pvt In ActiveSheet.PivotTables
For Each fld In pvt.PivotFields
For Each itm In fld.PivotItems
MsgBox itm
Next itm
Next fld
Next pvt
End Sub
What we’ve done in the above code is to recurse through all the pivots in the active sheet, all pivots fields in each of the pivots and finally through all the the pivot items in each of the pivot fields. If you wanted to just work with a single pivot, field or item, you can simply access it by using something similar to pivottable(i).pivotfield(j).pivotfield(k).select instead.
Once you know this much, the rest is fairly easy. Just bear in mind that rather than using pivottable.pivotfields you can be more specific.
Since we know that a pivot table has page fields, row fields, column fields and data fields, we can use pivottable.pagefields or pivottable.rowfields or pivottable.columnfields or pivottable.datafields. Let’s look at how each one works:
Accessing the page field:
Accessing the row field:
Accessing the column field:
Accessing the data field:
Keeping a tab on the index is very important….the index starts at 1 and not 0. So to access the second row field use index (2) and not index (1).
That was about reading the fields, what about reading the data contained within the pivot. Well that can be slightly tricky. VBA uses pivot.getpivotdata function to retreive values from the pivot. It is the same function that is used by Excel. The syntax, when used in Excel is GetPivotData ( data_field, pivot_table, “Field1”, “Property1”, “Field2”, “Property2″,……”FieldN”, “PropertyN” ). This when used in VBA is slightly different. You don’t need to specify the name of the pivot table as the function, by the virtue being called by a pivot object, ‘knows’ which pivot to return values from.
The syntax for GetPivotData function when used in excel :
GetPivotData ( data_field, pivot_table, “Field1”, “Property1”, “Field2”, “Property2″,……”FieldN”, “PropertyN” )
The syntax for GetPivotData function when used in VBA :
PivotTable.GetPivotData ( data_field, “Field1”, “Property1”, “Field2”, “Property2″,……”FieldN”, “PropertyN” )
The most important point is know that only those values can be retreived which appear on the excel worksheet. For example if the a row total has been turned ON for a pivot on the excel sheet, you can write the vba code to retreive the total for the row. However, the moment you turn the row total OFF, the vba formula will result in an error. (Tip: Use On Error Resume Next to override errors). You can’t write a single vba function that will return a single aggregated value from multiple cells. In short, always use a combination of column and row fields in the GetPivotData function that results in single cell value to be retrieved (that can also be located on screen).
The pivot hierarchy is :
PivotTable -> PivotField -> PivotItem
To read data from pivot:
Pivot.GetPivotData(“Row1”, “RowProperty1”, “Row2”, “RowProperty2″,………RowN”, “RowProperty2N” …………….”Column1″, “ColumnProperty1”, “Column2”, “ColumnProperty2″……… “ColumnN”, “ColumnPropertyN”)
Incase you missed it earlier, you can download an example of how to read data from pivot table using VBA here.
Prevent Users Ctrl Break (ing) Your VBA Code During Execution 
Table Formula in Excel (Something I didn’t Know Till Yesterday) 
Multithreaded VBA – An Approach To Processing Using VBScript 
Named Range – A Few Quickies 
So How Many Of These Excel Shortcuts Do You Know, Punk ? 
VBA IF Function – Using IF, ELSE:, ELSEIF, IF THEN in VBA code 
Very informative. And thanks for the example. Will try some of these on my dashboards.
November 13th, 2008 at 3:15 amHello,
I am trying to manage the format for a pivot table that we receive from a customer every week. In order for my macro to function correctly, I need to be able to format the pivot table to the expected format, and I’ve been able to do this for rows, autoformat, etc., but not for data or columns.
The data fields change once a month (which is expected), but I need to be able to remove any column fields without removing any data fields.
Therefore, I have the following code to remove column fields:
2
3
4
5
6
Dim pvtfield As PivotField
For Each pvtfield In pvtable.ColumnFields
pvtfield.Orientation = xlHidden
Next
End Sub
However, this also for some reason deletes my fields in pivotdata as well.
To compensate, I tried the following code:
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Dim pvtfield As PivotField
Dim datafields() As String
ReDim datafields(1 To 1)
For Each pvtfield In pvtable.datafields
storedatafields pvtfield.Name, datafields
Next
For Each pvtfield In pvtable.ColumnFields
If checkdatafields(pvtable.Name, datafields) = True Then
pvtfield.Orientation = xlHidden
End If
Next
End Sub
Private Function checkdatafields(pivottablename As String, datafields As Variant) As Boolean
Dim i As Integer
checkdatafields = False
For i = LBound(datafields) To UBound(datafields)
If datafields(i) = pivottablename Then
checkdatafields = True
Exit For
End If
Next
End Function
Private Sub storedatafields(ByRef pvfieldname As String, datafields As Variant)
Dim i As Integer
If datafields(1) = "" Then
i = UBound(datafields)
Else
i = i + 1
End If
ReDim Preserve datafields(1 To i)
datafields(i) = pvfieldname
End Sub
However, now it will not remove any column fields, thinking that they are data fields.
Is there any way in VBA to systematically differentiate between column fields and data fields so I can remove column fields while leaving data fields as is?
Thanks.
Regards,
William
August 12th, 2009 at 11:19 amWilliam,
Welcome to da TaB. I am certain that the first piece of code should work. I modified the code that you mentioned in the first block and it works fine.
2
3
4
5
6
7
8
9
10
Dim pvttable As PivotTable
Dim pvtfield As PivotField
For Each pvttable In ActiveSheet.PivotTables
For Each pvtfield In pvttable.ColumnFields
pvtfield.Orientation = xlHidden
Next
Next
End Sub
Here’s the workbook with code to remove columns from pivot table using vba. What you’ve got is the right code if all you want to do is to hide (or show) certain (or all) columns. If you remove all the columns, the data should simply aggregate with the final total remaining the same.
The second part of the code is unnecessary. pvttable.ColumnField should be sufficient to give you a handle to the column fields.
My only guess if that you may want to double check the object that you are passing as parameter to the function.
Regards,
August 12th, 2009 at 11:43 pmFirst of all congrats to this great article! Nice VBA coding, I really could use a lot of that!
But I still have one question, maybe You know the answer: Is it possible to get the address of the target of a GETPIVOTDATA-formula? I would like to use its address to start the Pivot Elements Drill-Down via a Hyperlink.
Hope I could express what I am searching for!
Regards,
October 29th, 2009 at 6:46 amJMP
JMP – That’s a very interesting question. I don’t have a ready answer for that but let me give it a try.
In the meanwhile could you please let me know whether you just have the cell where the GETPIVOTDATA is located or do you have the list of all the parameters to start with. If you have all the parameters separately available, one can try sorting through the entire pivot table column and row range and then try locating the cell that lies at their intersection. I agree that’s not the best of solutions but off hand that’s what I can think of till you provide me with more specifics or possibly a link to a file with the pivot table which I can have a look at.
Regards,
October 29th, 2009 at 2:03 pmDear Ajay,
sorry for my late reply.
Yes I have the cell located containing the GETPIVOTDATA formula. Unfortunatelly the targeted sheet contains various Pivot Tables. Therefore a Lookup scanning through the Rows and Columns is not possible, because the specific lables might occur several times within the sheet.
On the other hand does the GETPIVOTDATA formula contain a specific link to a used range (in this case the specific Pivot Table) due to its synthax. in this table the row and column lables just occur once. My problem is that my coding skills are too bad to realize this “Pivot-internal lookup” without making it fo ALL Pivots usable…
Hope I could express my problem in more detail.
Regards,
November 5th, 2009 at 4:46 amJMP
Jim,
Here’s something that should help.
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Dim cell_formula As String
Dim getpivot_link_start, getpivot_link_end As Long
Dim pvt As PivotTable
Dim rng2 As Range
cell_formula = rng.Cells(1, 1).Formula
getpivot_link_start = Application.WorksheetFunction.Find(",", cell_formula)
getpivot_link_end = Application.WorksheetFunction.Find(",", cell_formula, getpivot_link_start + 1)
For Each pvt In ActiveSheet.PivotTables
If InRange(Range(Mid(cell_formula, getpivot_link_start + 1, getpivot_link_end - getpivot_link_start - 1)), pvt.TableRange1) = True Then
get_pivot_area = CStr(pvt.TableRange1.Address)
End If
Next pvt
End Function
Function InRange(Range1 As Range, Range2 As Range) As Boolean
' returns True if Range1 is within Range2
Dim InterSectRange As Range
Set InterSectRange = Application.Intersect(Range1, Range2)
InRange = Not InterSectRange Is Nothing
Set InterSectRange = Nothing
End Function
Open the VBA IDE and place this code in a module. Now say if the pivot table is placed somewhere in the sheet and a particular cell (say “A15”) has the getpivotdata formula which refers to this particular pivot table, you can then simply enter =get_pivot_area(A15) in any cell. This User Defined Function (UDF) would return the entire area range of the pivot table to which the getpivotdata formula points to. You can then use the returned area range with a couple of OFFSET formulas.
Does this help
November 6th, 2009 at 12:00 amRegards,
I am trying to write VBA code that would drill down the data from a pivot table column or row total. I need to drill these totals down for further processing via VBA macro’s. However, I cannot seem to find a function that will drill down in VBA. Any suggestions?
May 22nd, 2010 at 10:14 pmFor me this is amazing…
January 13th, 2011 at 5:31 pmthanks a lot for helping us…
Ajay,
Is it possible to loop all fields in VBA such that all of them get added to the pivot table and summed (or counted, average etc.)? I regularly deal with 100+ variables, and it’s terribly painful to keep adding them one by one to the pivot table.
Thanks,
April 3rd, 2011 at 11:41 pmHi there,
found this page when searching for a solution to my problem.
But still not what I am looking for
My Problem:
I have an Access database with Input table, allocation queries and pivottables to get the data “summed if”.
Now I would like to export what the pivottable is showing but I somehow can’t get the data out of it….
Is it possible to create a table (in access) out of the other pivot table?
like:
company, account, total (this total is calculated in the pivot, same company same account)
i don’t know if it is understandable what I try to do
please come back if you need any further info
Thanks!
November 8th, 2012 at 8:20 amCheers
JD
Hi,
Very informative. I could implement my code using the article.
But I have ran into a problem. I have filtered the pivot table using a report filter. When I run the code it is picking all the rows that are not visible due to filter as well.
Please help.
November 30th, 2012 at 5:29 amUsing the sample “How to read data..” file I downloaded from your website, referring to pivot2, what is the correct VBA code if I am writing a macro to filter column F (Science) make on those scores above 65.00 visible? Thanks in advance.
March 13th, 2014 at 4:17 pmVery interesting and very useful article.
October 17th, 2014 at 8:56 amI found all necessary information to deal with pivottables from VBA.
Thanks a lot for sharing your EXCEL/VBA example so educational and efficient. ;o)
Initially, I was unable to use the code with Option Explicit invoked because there is no information relating to initializing the term “pvt”. I finally figured it out but I find failing to initialize variables an unfortunate and unnecessary common error in many published VBA code examples.
January 15th, 2015 at 8:25 amI dont understand this code:
its not working. How can you have pivotitems for a datafield??
January 10th, 2016 at 9:27 pmGreat article! Very, very helpful.
I applied the concepts here to write the following code to try returning the rowfield of a cell in the datafield. I wouldn’t think this would be that bad because I can see the field information when I hover over a cell and the contextures box is displayed. My code below worked just fine if the rowfield index and rowitem index are the same, and they usually are, but I came across a time where they were not the same. I thought that the index of the rowfield is changed whenever you add/remove a field from the row section of the pivot table. However, that didn’t seem to be the case when I added one field in particular – when I added it, all other rowfields’ indexes would adjust based on what position I put the new field in, but the new field would always have an index of 1, even if I put it in the 4th position. Are index and position not the same? Can you think of a way to pull the rowfield based on a cell in a data field you select?
The purpose of this is to pull the rowfield and rowitem (and columnfield, columnitem, pagefield, and pageitem eventually) to compile a SQL statement to query on an Access database if users want to see very detailed information relating to expenses for their budget.
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Dim NumOfRowItems As Integer
Dim NumOfColItems As Integer
Dim NumOfPageFields As Integer
Dim Field As PivotField
Dim ActualDrillActiveCell As Range
Set ActualDrillActiveCell = Sheets("ActualDrill SQL Build").Range("A1")
NumOfRowItems = ActiveCell.PivotCell.RowItems.Count
i = 1
Do Until i > NumOfRowItems
ActualDrillActiveCell.Value = ActiveCell.PivotTable.RowFields(i).Name
ActualDrillActiveCell.Offset(0, 1).Value = ActiveCell.PivotCell.RowItems(i).Name
ActualDrillActiveCell = ActualDrillActiveCell.Offset(1, 0)
i = i + 1
Loop
End Sub