PDA

View Full Version : Vba Pivot table



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

Aflatoon
06-04-2014, 05:58 AM
If you don't want that field, just don't add it to the table:


' Create the Pivot Table from the CacheSet 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("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 BlendVol").Caption = "Total Blend"


End With

AleemAM123
06-04-2014, 06:10 AM
But that column is volactivity, if I leave it out I'm not able to calculate BlendVol:

.CalculatedFields.Add "BlendVol", "=PeriodLength*VolActivity"

Paul_Hossler
06-04-2014, 06:29 AM
I'm not sure I understand but AFAIK you can hide a field but still use in in a calculated field



ActiveSheet.PivotTables("PivotTable1").PivotFields("CCC").Orientation = xlHidden

Aflatoon
06-04-2014, 06:42 AM
But that column is volactivity, if I leave it out I'm not able to calculate BlendVol:


What makes you say that? A field does not have to be part of a pivot table to be used in a calculated field.