The following code will refresh all pivot tables in an excel workbook or a worksheet. There are two ways to achieve this:

Refresh all pivots in Workbook – Method 1
Sub try1()
For Each pt In ActiveWorkbook.PivotCaches
pt.Refresh
Next pt
End Sub

Refresh all pivots in Workbook – Method 1
Sub try2()
For Each sht In ActiveWorkbook.Worksheets
For Each pt In ActiveSheet.PivotTables
pt.RefreshTable
Next pt
Next sht
End Sub