Sunday, 21 June 2015

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

ElseIf 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
      [ BI-Reporting Analyst]

1 comment:

  1. Thanks for sharing this Informative content. Well explained. Got to learn new things from your Blog onPower BI Online Training Bangalore

    ReplyDelete

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts