Automatically update Pivot Tables

[KymC]

I have tried to use the code below to update a number of Pivot tables in a workbook.

However I get the error message "Reference is not valid" when the macro gets to the code "pt.refreshTable".

I have tried different statements but still not able to get past this error - any ideas out there?

Thanks for the help.

Cheers

Kym



Sub RefreshAllPivots()
Dim wks As Worksheet
Dim pt As PivotTable

With Application
.Calculation = xlAutomatic
End With
With Application
.Calculation = xlManual
End With

For Each wks In Worksheets
For Each pt In wks.PivotTables
pt.RefreshTable
Next pt
Next wks

With Application
.Calculation = xlAutomatic
End With
End Sub

 

[SkipVought]

KymC,

You need to have this code run in a Module and NOT in a Sheet Object. I'm guessing that's wut's heppnin'



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KymC]

Skip,
Thanks for that suggestion, it switched my brain on and started me thinking.

I had it in a module but moved it to the workbook object and it worked fine.

Thanks for the help.

Cheers
Kym