Excel VBA Pivot Table Standard Operations

Pivot Table Standard Operations with Excel VBA
Suppose we have a Pivot Table as Follows :

Pivot Field list as follows :

On the above Pivot Table , we can perform various standard basic operations using VBA as follows :

Sub Pivot_Operations()

Dim Pvt As PivotTable
Dim Pvt_GrandTotal As Range

For Each Pvt In ActiveSheet.PivotTables

If Pvt.Name = "Pivot_Main" Then
Set MyPivot = ActiveSheet.PivotTables(Pvt.Name)
End If

Next Pvt
--------------------------------------------------------- 
'Setting the Grand Totals On for Columns and Rows
    With MyPivot
        .ColumnGrand = True
        .RowGrand = True
    End With
--------------------------------------------------------- 
'Changing the Pivot Layout to Classic Pivot Model , Displaying the FieldCaptions(Prod_Id, Sales_Period)

With MyPivot
    .InGridDropZones = True
    .DisplayFieldCaptions = True
    .RowAxisLayout xlTabularRow
End With

'Showing the Details of All data. The following method works good on Classic Pivot Tables , we can  convert a Pivot to Classic Pivot using above method.

Set Pvt_GrandTotal = MyPivot.GetPivotData("Sum of Sales")
Pvt_GrandTotal.ShowDetail = True
--------------------------------------------------------- 
'Showing the Details of All data. This method is not working properly(Getting Runtime Error 438)
MyPivot.DataBodyRange.Select
RC = (Selection.Rows.Count) - 1
CC = (Selection.Columns.Count) - 1

ActiveCell.Offset(RC, CC).Select
Selection.ShowDetails = True
--------------------------------------------------------- 
'Counting the PivotFields(ReportFilters,RowLabels,ColumnLabels)
PFC = MyPivot.PivotFields.Count

'Looping Thorough Pivot Fields ( Row Lables ,Report Filter, Column Labels , Values )
For X = 1 To PFC
'Displaying the Pivot Field Name
MsgBox MyPivot.PivotFields(X).Name

If MyPivot.PivotFields(X).Name = "Sales_Period" Then
'Counting the PivotItems of a Desired PivotField
PIC = MyPivot.PivotFields(X).PivotItems.Count

'Displaying the PivotFiled Orientation[Row Field(Orientaion=1),Column Field(Orientaion=2)]
MsgBox MyPivot.PivotFields(X).Orientation

'Looping through the specific Pivot Filed Items
For Y = 1 To PIC

'Checking whether the Desired Pivot Field is a Row Field/Column Field
If MyPivot.PivotFields(X).Orientation = xlRowField Then

'To Select the Specified Field Item Data Range
MyPivot.PivotFields("X").PivotItems(1).DataRange.Select
GT = (Selection.Cells.Count) + 1 'Count of Pivot Item Values

'Getting the Pivot < Row Field Item > and its Grand Total Value
MyPivot.PivotFields(X).PivotItems(Y).LabelRange.Select
MsgBox Selection.Value  'Pivot Item
MsgBox ActiveCell.Offset(0, GT).Value  'Grand Total of the Pivot Item

Else ' If MyPivot.PivotFields(X).Orientation = xlColumnField

'To Select the Specified Field Item Data Range
MyPivot.PivotFields("X").PivotItems(1).DataRange.Select
GT = (Selection.Cells.Count) + 1 'Count of Pivot Item Values

'Getting the Pivot < Colum Field Item > and its Grand Total Value
MyPivot.PivotFields(X).PivotItems(Y).LabelRange.Select
MsgBox Selection.Value 'Pivot Item
MsgBox ActiveCell.Offset(GT, 0).Value 'Grand Total of the Pivot Item

End If

Next Y

End If

Next X
--------------------------------------------------------- 
'To select the Report Filter
MyPivot.PageRange.Select

'To select the Cell of Sum of Sales/Net Sales.
MyPivot.DataLabelRange.Select

'To Select the Column/Row Header Label < Sales_Period >/<Prod_Id>
MyPivot.PivotFields("Sales_Period").LabelRange.Select

'To Select the Column/Row Label Items
MyPivot.PivotFields("Sales_Period").DataRange.Select

'To Select the Particular Field Item Label
MyPivot.PivotFields("Sales_Period").PivotItems("Q3-2014").LabelRange.Select

'To Select the Particular Field Item Data Range
MyPivot.PivotFields("Sales_Period").PivotItems("Q3-2014").DataRange.Select

'To Select the Row Grand Totals with Label < Grand Total >
MyPivot.PivotSelect "'Row Grand Total'", xlDataAndLabel, True

'To Select the Column Grand Totals with Label < Grand Total >
MyPivot.PivotSelect "'Column Grand Total'", xlDataAndLabel, True

'To Select the Intersection Value of Row and Column Item.
Intersect(MyPivot.PivotFields("Prod_Id").PivotItems("CDE_3456").DataRange.EntireRow, _
MyPivot.PivotFields("Sales_Period").PivotItems("Q3-2014").DataRange).Select

'To Select the Row Labels Range
MyPivot.RowRange.Select

'To Select the Column Labels Range
MyPivot.ColumnRange.Select

'To select the Data Section including Grand Totals.
MyPivot.DataBodyRange.Select
--------------------------------------------------------- 
'Applying Filter on Row/Column/Filter PivotFields
    With MyPivot.PivotFields("Prod_Id")
        .PivotItems("BCD_2345").Visible = False
        .PivotItems("DEF_4567").Visible = False
        .PivotItems("FGH_6789").Visible = False
        .PivotItems("GHI_7890").Visible = False
    End With

'Clearing Filters from Row/Column/Filter PivotFields
MyPivot.PivotFields("Prod_Id").ClearAllFilters
--------------------------------------------------------- 
'Changing the DataPivotField Caption
MyPivot.DataPivotField.PivotItems("Sum of Sales").Caption = "Net_Sales"

'Adding a < Row Field > to a Pivot
    With ActiveSheet.PivotTables("Pivot_Main").PivotFields("Prod_Id")
        .Orientation = xlRowField
        .Position = 1
    End With

'Adding a < Column Field > to a Pivot
    With ActiveSheet.PivotTables("Pivot_Main").PivotFields("Sales_Period")
        .Orientation = xlColumnField
        .Position = 1
    End With

'Adding a < Report Filter > to a Pivot
    With ActiveSheet.PivotTables("Pivot_Main").PivotFields("Sales_Region")
        .Orientation = xlPageField
        .Position = 1
    End With

'Adding a Field in < Values Section > of a Pivot
    ActiveSheet.PivotTables("Pivot_Main").AddDataField ActiveSheet.PivotTables( _
        "Pivot_Main").PivotFields("Sales"), "Sum of Sales", xlSum
    
    ActiveSheet.PivotTables("Pivot_Main").AddDataField ActiveSheet.PivotTables( _
        "Pivot_Main").PivotFields("Sales_Region"), "Count of Sales_Region", xlCount

'Changing the Function of a Value Field in < Values Section > of a Pivot :
    With ActiveSheet.PivotTables("Pivot_Main").PivotFields("Sales")
        .Orientation = xlDataField
        .Caption = "Count the Sales"
        .Function = xlCount
        .Position = 1
        .NumberFormat = "#,##0"
    End With

    With ActiveSheet.PivotTables("Pivot_Main").PivotFields("Sales")
        .Caption = "Sum of Sales"
        .Function = xlSum
        .Position = 1
        .NumberFormat = "#,##0"
    End With
---------------------------------------------------------   
'Removing the Grand Totals for Columns an Rows
MyPivot.ColumnGrand = False
MyPivot.RowGrand = False

'To Remove the <Net Sales>/<Sum of Sales>/Data Section along with Grand Totals
MyPivot.PivotFields("Net_Sales").Orientation = xlHidden


End Sub

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------