refresh pivottable not completely clearing values?


Board Regular
Mar 24, 2011
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")
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
.Name = "Test Cases " & TCStatus.Name
End With
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
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)
End If
Next pt
Next wks

any help appreciated- thank you- Becky


MrExcel MVP, Moderator
May 2, 2008
Office Version
365, 2019, 2016, 2010
Windows, MacOS
Have a look at this.


Board Regular
Mar 24, 2011
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!

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...