Vba Pivot table

[AleemAM123]

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]

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]

But that column is volactivity, if I leave it out I'm not able to calculate BlendVol:

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

[Paul_Hossler]

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]

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.