refresh multiple pivot tables in workbook
Hi
This single line should do it:
ActiveWorkbook.RefreshAll
Regards,
Per
On 6 Jan., 22:44, GaiGauci wrote:
Hi all. I have been trying to use this code I found from another answered
question from Matthew Herbert relating to refreshing pivot tables within a
workbook:
Dim Wks As Worksheet
Dim pvtTable As PivotTable
'don't allow screen updating because it takes time
Application.ScreenUpdating = False
'loop through each worksheet
For Each Wks In ActiveWorkbook.Worksheets
'loop through each pivot table in the worksheet
* * * * For Each pvtTable In Wks.PivotTables
'refresh the pivot table
* * * * * * pvtTable.PivotCache.Refresh
* * * * Next pvtTable
Next Wks
'tell the user you are done with the refresh
MsgBox "The pivot tables have been updated."
I have a large workbook and some sheets contain pivot tables and some don't
and they are from a variety of datasets- all within the workbook. I have
tried a number of different programatic solutions but they don't seem to hit
the mark. This one appears to be doing something but then I come up with an
error on "pvtTable.PivotCache.Refresh". I suspect that it might hit a
worksheet that doesn't have a pivot table. Can anyone tell me how to modify
this code to skip any sheet that doesn't have a pivot table, or can you tell
me if this code would cope with sheets without pivot tables??
Thanks for your help.
Gai
|