VBA: Create Pivot Table, execute calculation between two fields?

[MWhiteDesigns]

Good morning,
i have the below code to create a simple pivot table. I'd like to take the "Within SLA" field and divide it by the "Grand Total" Field.

I'd like to be able to do this within the pivot fields but I don't know if its possible since its two separate fields?

Thoughts?

Dim PCache As PivotCache, LastRow As Long, pt As PivotTable


  'If "Pivot" worksheet already exists, delete it

On Error Resume Next
Application.DisplayAlerts = False
Sheets("Summary").Delete
On Error GoTo 0
Application.DisplayAlerts = True

 Worksheets("Data").Activate
 Set PCache = ActiveWorkbook.PivotCaches.create(SourceType:=1, SourceData:=Range("A1").CurrentRegion.Address)
     Worksheets.Add
    ActiveSheet.Name = "Summary"
    ActiveWindow.DisplayGridlines = False
 Set pt = ActiveSheet.PivotTables.Add(PivotCache:=PCache, TableDestination:=Range("C2"), TableName:="PivotTable1")
     
 'Select fields for PivotTable
     ActiveWorkbook.ShowPivotTableFieldList = True


    With pt.PivotFields("LOB")
         .Orientation = xlRowField
         .Position = 1
     End With
  'Select Columns and Data
        With pt.PivotFields("Inside SLA")
         .Orientation = xlColumnField
         .Position = 1
     End With
     pt.AddDataField pt.PivotFields("Inside SLA"), "Within SLA", xlCount
     
     With pt.PivotFields("ALL SLA")
         .Orientation = xlColumnField
         .Position = 1
     End With
     pt.AddDataField pt.PivotFields("ALL SLA"), "Grand Total", xlCount
    
     ActiveWorkbook.ShowPivotTableFieldList = False

 

[MWhiteDesigns]

I thought maybe by adding a Calculated field and having the formula be: 'Inside SLA'/'All SLA' and all that returns is #DIV/0!

 

[MWhiteDesigns]

I tried the below but nothing happens.

pt.CalculatedFields.Add "Pull Through %", "=Inside SLA/All SLA", True

 

[RoryA]

Calculated fields in regular pivot tables always use SUM on the base fields - you can't use Count. It would be simpler to add some additional columns to your data source that return 1 for the relevant data, which you can then Sum and divide the results in your calculated field.

 

[MWhiteDesigns]

Thanks Rory. I've inserted helper columns that return a 1 for my data. When I test it and add the columns to the table manually I get what I am looking for.

Now how do I add the calculated using my vba method above?


='Within SLA'/'All SLA'

 

[RoryA]

You need to add the field, then put it in the table:

pt.CalculatedFields.Add("Pull Through %", "=Inside SLA/All SLA", True).Orientation = xlDataField

for example.

 

[MWhiteDesigns]

Thanks Rory, However when trying the given example the pivot table does not yield a new column to show the percentages and i get an error "Object variable or With Block variable not set"

Function Pivot()
Dim PCache As PivotCache, LastRow As Long, pt As PivotTable


  'If "Pivot" worksheet already exists, delete it
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Summary").Delete
On Error GoTo 0
Application.DisplayAlerts = True




 Worksheets("Data").Activate
 Set PCache = ActiveWorkbook.PivotCaches.create(SourceType:=1, SourceData:=Range("A1").CurrentRegion.Address)
     Worksheets.Add
    ActiveSheet.Name = "Summary"
    ActiveWindow.DisplayGridlines = False
 Set pt = ActiveSheet.PivotTables.Add(PivotCache:=PCache, TableDestination:=Range("C2"), TableName:="PivotTable1")
     
 'Select fields for PivotTable
     ActiveWorkbook.ShowPivotTableFieldList = True


     With pt.PivotFields("LOB")
         .Caption = "LOB"
         .Orientation = xlRowField
         .Position = 1
     End With
    
  'Select Columns and Data
     With pt.PivotFields("Within SLA")
         .Orientation = xlColumnField
         .Position = 1
     End With
     
     pt.AddDataField pt.PivotFields("Within SLA"), "Inside SLA", xlCount
     
     
     With pt.PivotFields("ALL SLA")
         .Orientation = xlColumnField
         .Position = 1
     End With
     pt.AddDataField pt.PivotFields("ALL SLA"), "Grand Total", xlCount
     
      
     
     pt.CalculatedFields.Add("Pull Through %", "=Inside SLA/All SLA", True).Orientation = xlDataField
    


    
     
     ActiveWorkbook.ShowPivotTableFieldList = False
     


 'Format PivotTable
 
     MsgBox "The macro has finished running.", vbInformation
 End Function

 

[RoryA]

The calculation should use "within SLA"

 

[MWhiteDesigns]

The calculation should use "within SLA"

Duh. How silly of me. Thank you.
2 things. How to actually format it to %. Also the Label shows "Sum of Pull Through %". How can I get it to just say "Pull Through %"

and to piggy back onto the naming, currently the name for the LOB shows "Row Labels". I want to replace that with LOB. I tried using .caption but it's not doing anything. What am I doing wrong?

With pt.PivotFields("LOB")
.Caption = "LOB"
.Orientation = xlRowField
.Position = 1
End With

 

[MWhiteDesigns]

Rory,
I was able to format it using the following.

pt.CalculatedFields.Add("Pull Through %", "=Within SLA/All SLA", True).Orientation = xlDataField

With pt.PivotFields("Sum of Pull Through %")
.NumberFormat = "0.0%"
End With

However I'd still like to know if changing the Name shown is possible. From "Sum of Pull Through %" to "Pull Through". Also the same goes for the 'LOB'.