VBA Express : Excel - Pivot Table Corrupting Workbooks

Excel

Pivot Table Corrupting Workbooks

Ease of Use

Easy

Version tested with

2007

Submitted by:

magelan

Description:

Filters being applied in a pivot table that is summarily erased can cause a corruption notification to appear.

Discussion:

Creating a pivot table based on data that is ever-changing, you will eventually reach this error if you apply filters to any of those pivot tables and then erase them.

Code:

instructions for use

Dim sheet As Worksheet Set sheet = Worksheets("Pivot") On Error Resume Next sheet.PivotTables("PivotTable").PivotSelect "", xlDataAndLabel, True Selection.Delete shift:=xlToLeft 'the above line erases the pivot table but you will see errors opening your workbook if you used filters sheet.PivotTables("PivotTable").ClearAllFilters ' add this line after the above line and you will no longer see the corruption

How to use:

Insert this line after any code in which you erase a pivot table.

Test the code:

If you see corruption, add this line after your pivot table erase code, and test again by saving and reopening your workbook. The corruption notifications should cease.

Sample File:

No Attachment

Approved by Jacob Hilderbrand

This entry has been viewed 14 times.