Buggy Pivot Tables?

[magelan]

Removed Records: PivotTable report from /xl/workbook.xml part (Workbook)


Has anyone seen this before? I have absolutely no Idea whats causing this, and it only started when I put viewing protection on my code.

[magelan]

Nevermind, I found the issue.

Excel likes to save your filters and deleting the pivot table doesnt get rid of them.

If you implement pivot tables in your code, make sure to add this

activesheet.PivotTables("your pivot table name").ClearAllFilters

[JWhite]

Really? I've never run into a problem with this but maybe I haven't seen the same conditions. Could you explain in a little more detail? Are you talking about deleting a pivottable and then creating a new one in the same workbook using the same pivotcache?

[magelan]

Really? I've never run into a problem with this but maybe I haven't seen the same conditions. Could you explain in a little more detail? Are you talking about deleting a pivottable and then creating a new one in the same workbook using the same pivotcache?

Here is my code that my pivot table is based on.



Sub buildPivot()
Dim wsheet As Worksheet

Set wsheet = ActiveWorkbook.Worksheets("Pivot") ' clear out pivot list if any
wsheet.Activate
On Error Resume Next
wsheet.PivotTables("PivotTable").PivotSelect "", xlDataAndLabel, True
Selection.Delete shift:=xlToLeft 'remove existing pivot tables if any
wsheet.PivotTables("PivotTable").ClearAllFilters

Set wsheet = ActiveWorkbook.Worksheets("List")
wsheet.Activate 'select the list sheet and make it active.
wsheet.UsedRange.cells.Select

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=wsheet.UsedRange, Version:=xlPivotTableVersion10) _
.CreatePivotTable TableDestination:="pivot!r3c1:r20c7", _
TableName:="PivotTable", DefaultVersion:=xlPivotTableVersion10
'the above block builds a cache and creates the pivot table

Dim pt As PivotTable
Set wsheet = ActiveWorkbook.Worksheets("pivot")
wsheet.Activate 'select the pivot sheet and format it for default
wsheet.Select
Set pt = wsheet.PivotTables("PivotTable")
pt.AddFields ColumnFields:="Date", RowFields:=Array("Entity", "Action")
pt.PivotFields("Action").Orientation = xlDataField
'pt.RowFields("action").ShowDetail = False
pt.RowFields("entity").ShowDetail = False
pt.PivotFields("Preparer").Orientation = xlPageField

End Sub

I also use Worksheet(pivot).cells.clearcontents a couple of times to blank out the pivot table if necessary. It turns out that the "xlPageField" Is what causes this error, so that whenever you blank out your pivot table, make sure you have

worksheet.PivotTables("PivotTable").ClearAllFilters

and you wont get those random errors!

EDIT: Basically because this program runs like a command center from which the user can pull up any year/month worksheets and run my calculations on them. So basically this program never changes though it might build 10-20 different lists/pivot tables.