Pivot Table - vba calculated.field not populating

  • 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


  • luckily was able to solve it . the formatting doesn't, if anyone knows the reason for that? It is not coming up as USD in red for negative.


    Code
    1. With ActiveSheet.PivotTables("ForwinPivotTable")
    2. .CalculatedFields.Add "GP/kg (Eur)", "= 'Gross Profit (EUR)'/ 'Export Chargeable Weight (kg)'"
    3. .PivotFields("GP/kg (Eur)").Orientation = xlDataField
    4. .Position = 1
    5. '.Function = xlSum
    6. .NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
    7. .Name = "GP/kg (EUR)"
    8. End With
  • I guess you put your On Error Resume Next back in? You're applying pivotfield properties to a pivottable object. Use:


    Code
    1. With ActiveSheet.PivotTables("ForwinPivotTable")
    2. .CalculatedFields.Add "GP/kg (Eur)", "= 'Gross Profit (EUR)'/ 'Export Chargeable Weight (kg)'"
    3. with .PivotFields("GP/kg (Eur)")
    4. .Orientation = xlDataField
    5. .Position = 1
    6. '.Function = xlSum
    7. .NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
    8. .Name = "GP/kg (EUR)"
    9. end with
    10. End With

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • rory yes, i couldn't figure out the R1C1 advice you gave me and was running against the clock ;) late for me to ask for help too :)


    Code
    1. Set PCache = ActiveWorkbook.PivotCaches.Create _
    2. (SourceType:=xlDatabase, SourceData:="'" & PRange.Worksheet.Name & "'!" & PRange.Address(ReferenceStyle:=xlR1C1)). _
    3. CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), _
    4. TableName:="ForwinPivotTable")


    changing the lines here still gives me a debug mismatch error on the last Tablename line.


    btw, the you code you had provided in response above works !! much appreciated.

  • Those should be two separate lines. The Pivotcaches.Create part makes a pivotcache, but you then apply the CreatePivotTable method to that, which actually then returns a PivotTable object, not a PivotCache. That's why you get a type mismatch when you try to assign it to the PCache variable.


    You should use something like:


    Code
    1. Set PCache = ActiveWorkbook.PivotCaches.Create _
    2. (SourceType:=xlDatabase, SourceData:="'" & PRange.Worksheet.Name & "'!" & PRange.Address(ReferenceStyle:=xlR1C1))
    3. Set PTable = Pcache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), _
    4. TableName:="ForwinPivotTable")


    and then in the rest of your code use PTable in place of ActiveSheet.PivotTables("ForwinPivotTable")

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why