refresh pivottable not completely clearing values?

[beckyinDC]

I have a pivottable.
pivotcache data gets updated and new rows added.
I have function to update datasource of each pivottable, and then it refreshes the pivottable.

I have another function that goes through all statuses (which are column data items) and creates a separate detail sheet for all tests of that status.

there are 4 statuses but not all are represented each time this is run...

PROBLEM: somehow my looping through statuses is looking for status values that are no longer present in dataset.

If Worksheets("DSR Parameters").chkGenTestCaseStatusDetail = True Then
With ActiveWorkbook.Worksheets("Details by Test Case Category")
.Unprotect
.Activate
.PivotTables(1).PivotCache.Refresh
For Each TCStatus In ActiveSheet.PivotTables(1).PivotFields("Status").PivotItems
txtTCSTatus = TCStatus.Name
MsgBox (txtTCSTatus) 'for testing
.PivotTables(1).PivotSelect txtTCSTatus, xlDataOnly
Selection.ShowDetail = True
With ActiveWorkbook.ActiveSheet
.Cells.EntireColumn.AutoFit
.Name = "Test Cases " & TCStatus.Name
End With
Next
End With
End If


...something isnt right when I look through front end spreadsheet at pivottable either - looking at the column selector I see the value that is no longer represented in the dataset anymore too.

So problem is probably in this code (which runs before the above)...maybe something needs to be added to address the cache more directly?

Public Sub refreshPivotTables()
Dim pt As PivotTable
Dim wks As Worksheet
Dim sData As String
Dim sourcewks As Integer
For Each wks In ActiveWorkbook.Worksheets
wks.Activate
wks.Unprotect
For Each pt In wks.PivotTables
sData = pt.SourceData
If sData Like "*TCCDATA*" Then
sourcewks = InStr(1, sData, "!")
sData = Left(sData, sourcewks)
pt.SourceData = sData & ActiveWorkbook.Worksheets("TCCDATA").UsedRange.Address(, , xlR1C1)
pt.PivotCache.Refresh
End If
Next pt
Next wks

any help appreciated- thank you- Becky

 

[RoryA]

Have a look at this.

 

[beckyinDC]

Rory- thank you kindly- I added in:


.PivotTables(1).PivotCache.MissingItemsLimit = xlMissingItemsNone

before the refresh and that works well.

I guess I could just set it to that value manually and expect it would be that way....but for now its in the code to ensure it is set right- seems safer.

thank you!