AleemAM123
06-02-2014, 05:04 PM
I am using some code from a VBA book by John Wakenbach to create a pivot table. It works great but I need a calculated result "BlendVol" but not "Blends". Is there a way to hide the Blends field from the pivot table?
' Create the Pivot Table from the Cache
Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PTcache, TableDestination:=Range("A1"), TableName:="PIMSBlends")
With PT
' Add fields
.PivotFields("CaseName").Orientation = xlPageField
.PivotFields("PeriodDescription").Orientation = xlPageField
'.PivotFields("ReportGroup").Orientation = xlRowField
.PivotFields("BlendTag").Orientation = xlRowField
.PivotFields("PeriodLength").Orientation = xlColumnField
.PivotFields("PeriodID").Orientation = xlColumnField
.PivotFields("CaseID").Orientation = xlColumnField
.PivotFields("VolActivity").Orientation = xlDataField
'.PivotFields("Actual").Orientation = xlDataField
'.DataPivotField.Orientation = xlRowField
' Add a calculated field to compute variance
.CalculatedFields.Add "BlendVol", "=PeriodLength*VolActivity"
.PivotFields("BlendVol").Orientation = xlDataField
' Specify a number format
.DataBodyRange.NumberFormat = "0.00"
' Apply a style
.TableStyle2 = "PivotStyleMedium15"""
' Hide Field Headers
.DisplayFieldCaptions = False
' Change the captions
.PivotFields("Sum of VolActivity").Caption = "Blends"
.PivotFields("Sum of BlendVol").Caption = "Total Blend"
End With
' Create the Pivot Table from the Cache
Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PTcache, TableDestination:=Range("A1"), TableName:="PIMSBlends")
With PT
' Add fields
.PivotFields("CaseName").Orientation = xlPageField
.PivotFields("PeriodDescription").Orientation = xlPageField
'.PivotFields("ReportGroup").Orientation = xlRowField
.PivotFields("BlendTag").Orientation = xlRowField
.PivotFields("PeriodLength").Orientation = xlColumnField
.PivotFields("PeriodID").Orientation = xlColumnField
.PivotFields("CaseID").Orientation = xlColumnField
.PivotFields("VolActivity").Orientation = xlDataField
'.PivotFields("Actual").Orientation = xlDataField
'.DataPivotField.Orientation = xlRowField
' Add a calculated field to compute variance
.CalculatedFields.Add "BlendVol", "=PeriodLength*VolActivity"
.PivotFields("BlendVol").Orientation = xlDataField
' Specify a number format
.DataBodyRange.NumberFormat = "0.00"
' Apply a style
.TableStyle2 = "PivotStyleMedium15"""
' Hide Field Headers
.DisplayFieldCaptions = False
' Change the captions
.PivotFields("Sum of VolActivity").Caption = "Blends"
.PivotFields("Sum of BlendVol").Caption = "Total Blend"
End With