Refresh all pivottables when deactive data sheet

[maabadi]

Hi
I have this deactive macro for Sheet1 (data sheet)(create by right-click on sheet name and insert module):

Private Sub Worksheet_Deactivate()
    Run "PivotMacro"
End Sub

and this is Pivotmacro :

Sub PivotMacro()
Dim pt As PivotTable
    For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ActiveSheet.PivotTables
        pt.RefreshTable
    Next pt
  Next ws
End Sub

when I run pivotmacro is working and see result changed, but when I change or add data on sheet1 (data sheet) and go to other sheets, I see the results don't change and macro didn't work. And also don't show errors and if run pivotmacro, it is work perfectly?! why?

Please Help

 

[RoryA]

Your pivot macro code is not actually correct. It should read:

Sub PivotMacro()
Dim pt As PivotTable
    For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
        pt.RefreshTable
    Next pt
  Next ws
End Sub

 

[maabadi]

Hi RoryA
I apply your modifications but the results are same.
And my sheet name and product name is persian name. Are they effect on macro?

Your pivot macro code is not actually correct. It should read:

Sub PivotMacro()
Dim pt As PivotTable
    For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
        pt.RefreshTable
    Next pt
  Next ws
End Sub

 

[RoryA]

Where did you put the Worksheet_deactivate code?

 

[maabadi]

Where did you put the Worksheet_deactivate code?

Right-click on sheet1(Data sheet) and Insert module and then put there.

 

[RoryA]

No you shouldn't insert a module. Right click the sheet tab, choose View code, then paste it into the module that is already there.

 

[maabadi]

No you shouldn't insert a module. Right click the sheet tab, choose View code, then paste it into the module that is already there.

Thanks RoryA. that's it. it works very well.