refresh pivottable not completely clearing values?

beckyinDC

Board Regular
Joined
Mar 24, 2011
Messages
56
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

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

beckyinDC

Board Regular
Joined
Mar 24, 2011
Messages
56
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

Threads
1,081,702
Messages
5,360,743
Members
400,595
Latest member
T_Dubs

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...
Top