Pivot Table VBA: VBA to Read & Modify a Pivot Table in Excel




Here is a collection of VBA codes to work with Pivot Tables:

1. Create a pivot table using a VBA Macro
2. Delete all Pivot Tables Using a VBA Macro
3. Refer to a Pivot Table directly within a VBA Macro
4. Refer to a Pivot Table Field directly within a VBA Macro
5. Refer to a Pivot Item of a Pivot Field present in a Pivot Table directly using a VBA Macro
6. Remove old (ghost) Items from a Pivot Table using VBA Macro
7. Refresh All Pivot Tables in a Workbook using VBA Macro
8. Refresh All Pivot Tables in an Excel Sheet using VBA Macro
9. Extract Data (Expand) from a Pivot Table using a VBA Macro
10. Remove or Include Pivot Fields in a Pivot Table using a VBA Macro
11. Hide Pivot Items of Pivot Field located in a Pivot Table using a VBA Macro
12. Moving and change orientation of Pivot Fields in a Pivot Table using VBA Macro
13. Reduce time taken to update a pivot table when using a VBA macro

Here’s the source data for all the examples:

pivot-table-and-vba-macro-source-data

Create a pivot table using a VBA Macro

The sample vba code shown below helps create a pivot table.

1
2
3
4
5
6
7
8
9
Sub create_pivot()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:="Sheet3!data").CreatePivotTable _
TableDestination:=ActiveSheet.Cells(10, 10), _
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables(1).AddFields RowFields:="Name"
ActiveSheet.PivotTables(1).PivotFields("Name").Orientation = xlDataField
End sub

Parts of this VBA macro that you may want to change:
SourceData = Provide a range that contains the underlying data for the pivot table. In its present form, the code simply create a pivot table by picking up all the used cells present near the active cell.
TableName = Give the Pivot Table a name that you like
TableDestination = The cell where you’d like to place the pivot table. Format to use= ‘[filename.xls]Sheet1’!R1C1

Delete all Pivot Tables Using a VBA Macro

1
2
3
4
5
6
Sub Delete_All_Pivot_Tables_In_Sheet()
For Each pvt In ActiveSheet.PivotTables
    pvt.PivotSelect "", xlDataAndLabel, True
    Selection.ClearContents
Next pvt
End Sub

Refer to a Pivot Table directly within a VBA Macro

1
2
3
4
5
6
7
8
Sub Refer_Table()
Dim pvt As PivotTable
Set pvt = ActiveSheet.PivotTables(1) 'Referring to Pivot Table with Index 1
Set pvt = ActiveSheet.PivotTables("PivotTable1") 'Referring to pivot table with Pivot Table Name
MsgBox pvt.Name
MsgBox ActiveSheet.PivotTables(1).Name
MsgBox ActiveSheet.PivotTables("PivotTable1").Name
End Sub

You can either set the pivot table to an object or refer it directly using the index or the pivot table name.

Refer to a Pivot Table Field directly within a VBA Macro

1
2
3
4
5
6
7
8
Sub Refer_Pivot_Table_Field()
Dim pvt_fld As PivotField
Set pvt_fld = ActiveSheet.PivotTables(1).PivotFields(1)
Set pvt_fld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales")
MsgBox pvt_fld.Name
MsgBox ActiveSheet.PivotTables(1).PivotFields(2).Name
MsgBox ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales").Name
End Sub

Again, you can either set the pivot field to an object or refer it directly using the index or the name of the pivot field in the macro.

Refer to a Pivot Item of a Pivot Field present in a Pivot Table directly using a VBA Macro

1
2
3
4
5
6
7
8
Sub Refer_Pivot_Table_Item()
Dim pvt_itm As PivotItem
Set pvt_itm = ActiveSheet.PivotTables(1).PivotFields(1).PivotItems(1)
Set pvt_itm = ActiveSheet.PivotTables("PivotTable1").PivotFields("Name").PivotItems("Adrian")
MsgBox pvt_itm.Name
MsgBox ActiveSheet.PivotTables(1).PivotFields(1).PivotItems(1).Name
MsgBox ActiveSheet.PivotTables("PivotTable1").PivotFields("Name").PivotItems("Adrian")
End Sub

Remove Old (ghost) Items from a Pivot Table using VBA Macro

Often times you will items show up in the list drop down of a particular field in pivot table even though those values do not exist in the pivot table. More likely than not, these are old items left over from the earlier data in the pivot table’s cache and still continue to appear in the table. As a good practice, if you have large amounts of data in a pivot table and that table gets updated with fresh data frequently, you may want to call this procedure to clean up the pivot table.

1
2
3
4
5
6
Sub Remove_Old_Items()
For Each pvt In ActiveSheet.PivotTables
pvt.PivotCache.MissingItemsLimit = xlMissingItemsNone
pvt.PivotCache.Refresh
Next pvt
End Sub

Refresh All Pivot Tables in a Workbook using VBA Macro

The classic case of refreshing all pivot tables in a workbook in one go. A must when you have more than a handful of pivot tables in the workbook :-)

1
2
3
4
5
Sub Refresh_All_Pivots_1()
For Each pvt In ActiveWorkbook.PivotCaches
     pvt.Refresh
Next pvt
End sub

OR

1
2
3
4
5
6
7
Sub Refresh_All_Pivots_2()
For Each sht In ActiveWorkbook.Worksheets
For Each pt In sht.PivotTables
     pt.RefreshTable
Next pt
Next sht
End Sub

Refresh All Pivot Tables in an Excel Sheet using VBA Macro

1
2
3
4
5
Sub Refresh_All_Pivots_In_Sheet()
For Each pvt In ActiveSheet.PivotTables
    pvt.PivotCache.Refresh
Next pvt
End Sub


This is the simple method of expanding a pivot table so that the underlying data is revealed. This requires that the pivot table store a copy of the underlying data set with it for it to work well.

Extract Data (Expand) from a Pivot Table using a VBA Macro

1
2
3
4
5
6
7
8
9
10
Sub Extract_Data_From_Pivot()
For Each pvt In ActiveSheet.PivotTables
With pvt
        .ColumnGrand = True
        .RowGrand = True
End With
Set rng = pvt.DataBodyRange
rng.Cells(rng.Rows.Count, rng.Columns.Count).ShowDetail = True
Next pvt
End Sub

Remove or Include a Pivot Field in a Pivot Table using a VBA Macro

Often times we need to remove a particular field from the pivot table. The following code will help you achieve it.

1
2
3
4
5
6
7
Sub Hide_Fields()
For Each pvt In ActiveSheet.PivotTables
    For Each pvtfld In pvt.PivotFields
        pvtfld.Orientation = xlHidden
    Next pvtfld
Next pvt
End Sub

In the above code, we have tried to hide all pivot fields in a pivot table. You may want to selectively remove fields by referring to them either by their index values or by their names. Example:
ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Name”).Orientation = xlHidden

To include the fields again and have them display in the pivot table, use this code:

1
2
3
4
5
6
7
8
9
10
11
Sub Show_Fields()
For Each pvt In ActiveSheet.PivotTables
    For Each pvtfld In pvt.PivotFields
    'Use any of the four options listed here
       'pvtfld.Orientation = xlRowField
       'pvtfld.Orientation = xlColumnField
       'pvtfld.Orientation = xlPageField
       pvtfld.Orientation = xlDataField
    Next pvtfld
Next pvt
End Sub

Hide Pivot Items of Pivot Field located in a Pivot Table using a VBA Macro

The following code will help you turn off a field from the drop-down of a particular field in a pivot table

1
2
3
4
5
6
7
8
9
10
Sub Hide_Items()
On Error Resume Next
For Each pvt In ActiveSheet.PivotTables
For Each pf In pvt.PivotFields
For Each pit In pf.PivotItems
    pit.Visible = False
Next pit
Next pf
Next pvt
End Sub

By default, atleast one pivot item needs to be left turned if you decide to hide items in the drop down of a field in a pivot table. Other wise you will have excel throwing an error which says : Run-time error ‘1004’: Unable to set the Visible property of the PivotItem class. To ensure that we do not have the error blocking the code during runtime, we insert the On Error Resume Next in our code. (You may want to read more about error handling in VBA here). To make the fields show again, simply turn them on by replacing the pit.Visible = False statement with pit.Visible = true.

Moving and change orientation of Pivot Fields in a Pivot Table using VBA Macro

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub Move_Fields_Of_Pivot_Table()
For Each pvt In ActiveSheet.PivotTables
For Each pvtfld In pvt.PivotFields
pvtfld.Orientation = xlHidden
Next pvtfld
Next pvt
For Each pvt In ActiveSheet.PivotTables
pvt.PivotFields("Target").Orientation = xlDataField
pvt.PivotFields("Product").Orientation = xlRowField
pvt.PivotFields("Month").Orientation = xlColumnField
pvt.PivotFields("Manager").Orientation = xlPageField
Next pvt
End Sub

Reduce time taken to update a pivot table when using a VBA macro

Pivot tables can be notoriously slow when you work with large data sets in VBA. Moving, updating and hiding items can a long time to happen. To reduce update and response time when working with Pivot Tables in VBA, try setting the update option to manual. Manual update can reduce the time taken to update and work with a pivot table by around 10% for very small tables (say 10 rows in source data) to as much as 75% in large tables (with more than 50,000 rows in source data).

1
2
3
4
5
Sub Set_Manual_Update_On
ActiveSheet.PivotTables(1).ManualUpdate = True 'At the beginning of code
'YOUR CODE HERE
ActiveSheet.PivotTables(1).ManualUpdate = False 'At the end of code
End Sub

You can test the improvement in speed by say comparing both the pieces of VBA code shown below. The first piece of the code will run much slower than the later.

1
2
3
4
5
6
7
8
9
10
11
12
Sub Manual_Update_Off()
t = Timer
For i = 1 To 20
On Error Resume Next
For Each pf In ActiveSheet.PivotTables(1).PivotFields
For Each pit In pf.PivotItems
    pit.Visible = False
Next pit
Next pf
Next i
MsgBox Timer - t
End Sub

This one will work much faster.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub Manual_Update_On()
t = Timer
ActiveSheet.PivotTables(1).ManualUpdate = True
For i = 1 To 20
On Error Resume Next
For Each pf In ActiveSheet.PivotTables(1).PivotFields
For Each pit In pf.PivotItems
    pit.Visible = False
Next pit
Next pf
Next i
ActiveSheet.PivotTables(1).ManualUpdate = False
MsgBox Timer - t
End Sub

You can download an example of the pivot table and vba macro code here or click on the button below:
pivot-table-and-vba-macro


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>


Comments and Trackbacks

  1. JonnyC wrote:

    Awesome code took me a while to figure out but is a great way to create dynamic named ranges within vba for use with PivotTable source data
    Sub SetDynamicRange()

    ‘ 8/24/2010 by Jonathan Cavallo

    Dim Rng1 As Range

    ‘Change the range of cells (A1:B15) to be the range of cells you want to define
    Set Rng1 = Sheets(“N-Sar 25 Prin”).Range(“A1”, Range(“A1″).End(xlToRight).End(xlDown))
    ActiveWorkbook.Names.Add Name:=”DataSource1”, RefersTo:=Rng1

    End Sub

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel


ARCHIVES