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

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

Thanks Per. I tried that earlier and it didn't seem to work, but it does now.
I think I'm suffering VBA fever from looking at this too much.

Thanks again.
Gai

"Per Jessen" wrote:

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

.