Pivot Date filter not working

[Ken]

Below VBA code does the process.
However, it does no show any data after completing the code process. Only way to see data in pivot rows is to manually drag date field in row and bringing it back up in filter field of pivot, after that it shows data - Anything missing in the below code ?


Testing result in Excel 2003 -
Does not display any data in pivot, even though the date field shows the range date selected. Only way it shows pivot data when you manually refresh or drag the date field in the row.

Testing result in Excel 2007 -
Does not show any data at the bottom plus on check pivot date field - the filter shows checkmark not only for date range entered but also for any month or year with days between 1 to 9.



1) Dates stored in pivot table as m/dd/yyyy
i.e. 6/02/2010, 6/01/2010, .... 5/15/2010...

(if date range entered is from 6/01/2010 to 6/30/2010. The above code will make selection in pivot from june 1st to june 30th 2010 and also for may 1st to may 9 2010, .....upto data that is available in pivot.)

2) Have tried changing system date for short date as m/dd/yyyy also tried format command for date in the code still the same - no effect in excel 2007 still shows date selected from 1 to 9 for any year.

Sub Pivot_DateFilter()

    Dim PivtTbl As PivotTable
    Dim PivtFld As PivotField
    Dim PivtItm As PivotItem
    
    
On Error Resume Next

    Worksheets("Sheet1").Activate
        
    Set PivtTbl = ActiveSheet.PivotTables("Table_Pivot1")
    Set PivtFld = PivtTbl.PivotFields("Date")
    
    PivtTbl.ManualUpdate = True
    PivtFld.EnableMultiplePageItems = True
        
    For Each PivotItem In ActiveSheet.PivotTables(PivtTbl). _
                                      PivotField("Date"). _
                                      PivotItems
        
        If PivtItm.Value >= From_Date And _
           PivtItm.Value <= To_Date Then
           PivtItm.Visible = True
        Else
           PivtItm.Visible = False
        End If
    
    Next PivotItem
    
            
    PivtFld.EnableMultiplePageItems = False
    PivtItm.ManualUpdate = False
    
    Set PivtFld = Nothing
    Set PivtItm = Nothing

End Sub

 

[combo]

First of all try:

For Each PivtItm[s]PivotItem[/s] In ActiveSheet.PivotTables(PivtTbl). _
                                      PivotField("Date"). _
                                      PivotItems

combo

 

[SkipVought]

Hi,
Dates are NUMBERS, that are only DISPLAYED as short or long or any number of different ways that you could imagine.

For instance, today's Date Serial Value is 40428: that is fourty thousand, four hundred and twenty eight days since December 31, 1899; January 1, 1900 being Date Serial Value 1.

So FORMAT is just window dressing. It's the NUMBER that counts, er uh literally!

Please explain in more detail, the date issues that you are having.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

Continuing my previous post, you cannot use object (ActiveSheet.PivotTables(PivtTbl)...) instead of index/name when referring to collection item, in this case should be:

[tt]For Each PivtItmP In PivtTbl.PivotField("Date").PivotItems[/tt]

I'd replace the On Error Resume Next statement by other error handler, at least wnen working with the code. It makes debugging the code difficult.



combo