Excel Made Easy

Vba pivot tables in vba in Excel

In the previous chapter, the fundamentals of the pivot tables were discussed.

In this chapter, we will see, how to automate it using visual basic editor.

In VBA, the "PivotTable" object is used to work with pivot tables

The "PivotTable" object is a member of PivotTable Collections.

To access a pivot table in excel, the index number of the pivot table is used.

Let us consider the pivot table of previous chapter and explore some of the concepts of it in VBA.

1.AddDataField

This method adds a new data field to the pivot table report.

The data field added should be a member of the pivot table.

The following example illustrates this:

The above example adds "Women" column in the pivot table report.

2.ClearAllFilters

This method clears all filters currently applied to the pivot table.

This includes both PivotFilters collection and manual filtering applied to the pivot table.

The following code demonstrates this:

3.ClearTable

The�ClearTable�method is used for clearing a PivotTable. Clearing PivotTables includes removing all the fields and

deleting all filtering and sorting applied to the PivotTables. This method resets the PivotTable to the state it had right after it was created,

before any fields were added to it.

The following code demonstrates this

4.RefreshTable

This method refreshes a pivot table with the source data.

Refreshing means, updating the pivot table with latest data of the source range.

The following example shows this:

Note that, the result of the above snippet will be visible only there is any change in the source range.

The screenshot of code and pivot table is shown below:

You can find similar Excel Questions and Answer hereunder

1) How can I find the number of working days between 2 dates using VBA?

2) How can I set the Source Data of charts using VBA?

3) Excel 2010 vba replacement of application filesearch in Excel

4) How do I add a symbol like Triangle / Inverted Triangle for indicating trends in a cell using VBA?

5) How can I add a legend to a chart using VBA?

6) How to do webscrapping in VBA. Here some basics

7) How can I extract file name from a full path including folder path and file name?

8) Filtering the value field in a pivot table in Excel

9) How to copy files in Excel VBA is explained here

10) The name manager in Excel is very useful to keep an overview and manage all the variable names in Excel. Here how to use the name manager in VBA