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
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
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.