Here's the situation.
Our team is the so called compliance specialist, we test all the processes that the operations doing. To make the story short our final product is to make solutions to the defects that our team came up with.
We are using the pivot table as our defect tracker, the problem is, my boss wants the pivot table to be instantly updated upon updating the data source. Yes there is this option that when you reopened the file the pivot table will be refreshed. And I know there is this VB function and Im using this code

Private Sub Worksheet_Calculate()
Dim pt As PivotTable
Application.EnableEvents = False
For Each pt In Sheets("Defects tracker").PivotTables: pt.PivotCache.Refresh: Next pt
Application.EnableEvents = True
End Sub

It worked at the first time I executed it, upon updating the data source(sheet2), once I click the sheet1, where the pivot table is, its automatically refresh, that is instantly.
However it just worked ONCE, upon closing the file, and reopened it, VB function wont work anymore. even if I edit the code, change the code, nothing happened. Just that it wont work anymore..

If there's something I missed please let me know, any help will be much appreciated. thanks.