pivot table refresh code failed

[dreamz]

i'm trying to write some code so that a change in the source tabl
triggers a refresh process for all my pivot tables (on other sheets).

so far, i've tried something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim PT As PivotTable
Application.EnableEvents = False
For Each PT In Me.PivotTables
PT.RefreshTable
Next PT
Application.EnableEvents = True
End Sub

it doesn't automatically update, but all the pivot tables get refreshe
once i refresh one of them.

i've also tried:

Private Sub Worksheet_Calculate()
blah.PivotTables("PivotTable1").PivotCache.Refresh

but i get an error:

Run-time error '1004':
Method 'PivotTables' of object'_Worksheet' failed

any ideas?

thanks