XL 2010 Pivot Table Refresh Error

[MeGustaXL]

Hi,

My workbook has a total of 19 Pivot Tables, linked to various Data Tables. When I do a "Refresh All", a message pops up saying that "This Pivot Table is invalid...."
When I close and reopen the workbook, it gets repaired because of an "Invalid Pivot Table", and the xml report points to PivotTable8, but PT8 refreshes just fine from the ribbon and right-click Refresh buttons. I've tried deleting the entire PT and re-making it, but it still fails.

Any ideas?

Chris

Someday I'll know what I'm donig...damn!

 

[SkipVought]

Hi,

I think that I’ve seen something like this. It’s been during 25+ years.

But I do remember that I often looped through the PTs in my workbooks to refresh, rather than doing Refresh All.

Dim ws as Worksheet
Dim pt as PivotTable 

For Each ws In Worksheets
   For Each pt In ws.PivotTables
      With pt.PivotCache
         .MissingItemsLimit = xlMissingItemsNone
         .Refresh
      End With
   Next
Next

It may be overkill, but it seemed to work for me.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MeGustaXL]

Thanks Skip, tried that and it Refreshed them all nicely, but closing and reopening still kicks off a repair of PivotTable8, even though the entire tab with it on has been deleted

Chris

Someday I'll know what I'm donig...damn!

 

[SkipVought]

Check out this thread regarding the MissingItemsLimit property.

https://www.tek-tips.com/viewthread.cfm?qid=699012

See my revised code above.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

@MwGustaXL,

Did you do anything with the MissingItemsLimit property?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Tweenster]

Hi Skip,

I did try your code, but I still get this error on re-opening:

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<recoveryLog xmlns="

http://schemas.openxmlformats.org/spreadsheetml/2006/main">

<logFileName>error111400_02.xml</logFileName><summary>Errors were detected in file 'C:\Users\****** .xlsm'
</summary><repairedRecords summary="Following is a list of repairs:">
<repairedRecord>Repaired Records: PivotTable report from /xl/pivotTables/pivotTable8.xml part (PivotTable view)</repairedRecord>
</repairedRecords>
</recoveryLog>

Even when I deleted Pivot table 8, it came up with the same error....

Many Thanks.

 

[SkipVought]

You may still have a PivotCache. Do you have other PTs in your workbook?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MeGustaXL]

Skip,

Sorry for the delay but we've been diverted for the last few days.
Tweenster and I have tried the Missing Items property and clearing the Pivot Caches, manually and by VBA, but the error still occurs every time the WS is opened.
Any chance it could be due to the sheer gigantic size of the workbook?



Chris

Someday I'll know what I'm donig...damn!