Pivot table update zeros out UDF

[rguy84]

I have a function that I made with help from here that sums up time.

Function totalTime()
    Dim lastCell As Long
    
    'Goes to last row of the column
    lastCell = Range("M" & Rows.Count).End(xlUp).Row
    totalTime = WorksheetFunction.Sum(Range("M2:M" & lastCell))
    
    Application.Volatile
End Function

At the minute P2 says 00:45 minutes - which is correct.

On sheet 3 (stats) [i deleted sheet 2], I have a PivotTable based on sheet one. After noticing the PT doesn't auto-update, I made a button on the spreadsheet, and made the following macro:

Sub refresh_click()
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables("HDstats")
    pt.RefreshTable
End Sub

On click my PT updates, but my value on sheet1.P2 goes to 00:00. doing a recalculate on p2 brings back my value.

Any hints why?

 

[rguy84]

bump (currently on pg 18)

 

[rguy84]

I think the pivot table is doing something to the cells it is referencing. Since the pivot table is on it's own page, my UDF function doesn't know to update, even though it Application.Volatile. I added Application.CalculateFullRebuild to the macro above, but didn't work.