refresh multiple pivot tables in workbook

G

GaiGauci

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
 
G

GaiGauci

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top