I hit a road block and cannot figure out why the calculated field at the end of the code is not populating?
thanks for any pointers
Code
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim lastrow As Long
Dim LastCol As Long
Dim pvtFld As PivotField
'Declare Variables
Application.DisplayAlerts = False
Worksheets("ForwinPivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "ForwinPivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("ForwinPivotTable")
Set DSheet = Worksheets("Forwin")
'Define Data Range
lastrow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(lastrow, LastCol)
'Define Pivot Cache
On Error Resume Next
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), _
TableName:="ForwinPivotTable")
'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="ForwinPivotTable")
'Insert Row Fields
With ActiveSheet.PivotTables("ForwinPivotTable").PivotFields("ATA")
.Orientation = xlRowField
.Position = 1
End With
'Insert Data Field
With ActiveSheet.PivotTables("ForwinPivotTable").PivotFields("Gross Profit (EUR)")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "GP (Eur) "
End With
'Insert Data Field
With ActiveSheet.PivotTables("ForwinPivotTable").PivotFields("Export Chargeable Weight (kg)")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Total Charg. Weight "
End With
With ActiveSheet.PivotTables("ForwinPivotTable")
.CalculatedFields.Add "GP/kg (Eur)", "= 'GP (Eur) '/ 'Total Charg. Weight '"
.PivotFields("GP/kg (Eur)").Orientation = xlDataField
End With
Display More