How to manipulate Pivot Fields in Pivot Table using VBA

Once you have created the Pivot table using the Pivot Cache to place the data in the memory, you can now start manipulating the Pivot Fields in the Pivot Table using VBA. The complete code with explanations is given below:
Manipulating PivotFields using VBA
Sub addFields()
With ActiveSheet.PivotTables(1)
‘First row field
With .PivotFields(“Name”)
.Orientation = xlRowField
.Position = 1
End With

‘Report Filter field
With .PivotFields(“Location”)
.Orientation = xlPageField
.Position = 1
End With

‘Order Amount or numerical data in the Values field
.AddDataField ActiveSheet.PivotTables(1).PivotFields(“Order Amount”), _
“Sum of Amount”, xlSum
End With
End Sub

Watch the Excel training video:

Further reading:
Using VBA to Manipulate PivotTable Reports

2 thoughts on “How to manipulate Pivot Fields in Pivot Table using VBA

  1. Hi Sir,

    I have a problem about VBA code.
    Can I pull the particular data from HTML sheet a with no table.
    I have information in HTML sheet like MS word format.
    I have to pool particular data from specific keywords which is occure in three to four times in HTML file. I have to pull all data with that specific keyword.
    Please help me for this problem with particular example.

    Rahul T. (Pune, India)

  2. I have a pivot table with multiple pivotfields, I need to filter each pivotfield with the specific items that should be marked. Although the code used is useful for one, I am not able to do it on various pivotfields at the same time.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.