locked
Excel 2013 Pivot Table Counts Don't Update with New Filter RRS feed

  • Question

  • Hi:

    I'm using a VBA Macro to select a date range for data in a pivot table.  It works fine as long as I show all rows.  Only the rows in the date range I select appear.  I change the dates and run the macros and the pivot table changes to show the correct items.  The problem occurs when I add a Count aggregate column.  Now, no matter what dates I select, the counts are for ALL of the data, not just the rows I am trying to filter.  it seems that any pivot table with an aggregate ignores filters added via VBA.  Can someone tell me what I am missing?  My code is below. 

    Thanks,

    Sub FilterPivot()
        Dim PT As PivotTable
         
        Dim dteStart As Date
        Dim dteEnd As Date
        
        dteStart = Worksheets("Sheet1").Range("B3")
        dteEnd = Worksheets("Sheet1").Range("B4")
                
        For Each PT In Worksheets("PivotTable").PivotTables
            
           With PT.PivotFields("CreatedDate")
               For Each pfltr In .PivotFilters
                   pfltr.Delete
               Next pfltr
               .PivotFilters.Add Type:=xlDateBetween, Value1:=dteStart, Value2:=dteEnd
                
           End With
           Worksheets("PivotTable").Select
           PT.PivotCache.Refresh
            
        Next PT
        
        Worksheets("Sheet1").Select
    
    End Sub
    


    TerryL

    Monday, March 4, 2013 6:45 PM

All replies

  • Note that the filter symbol does appear next to the CreatedDate column on the far right.  The count is always 4, regardless of the filter dates.  And I have also tried to PivotTable.RefreshTable method without success.


    TerryL

    Monday, March 4, 2013 7:00 PM
  • This is a quick note to let you know that we are doing research on this issue. We will let you know if there is any progress.

    Thanks for your understanding and have a nice day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, March 6, 2013 9:46 AM
  • Hi TerryL,

    I tried to reproduce the problem using your code but unfortunately, I was unable to.  My filtered results were correct, even when using an aggregate.  If you are able to add a sample file demonstrating the problem, we can take a look.  Otherwise, you may want to open up a support case with us so we can investigate further. 


    Sharon M, Microsoft Online Community Support

    Tuesday, March 12, 2013 3:07 AM