1

I have already referred to a few other threads relating to this issue, but none of the answers have resolved my problem. I'm trying to select Pivot Items in a Pivot Field whose dates are equal to yesterday, the day before yesterday, and two days before yesterday.

I have tried changing my code so that I only ever write that xxx.Visible = False (as opposed to true), refreshing the table, and changing the "Number of items to retain per field" pivot table option to "None."

Here is my current code - any suggestions would be greatly appreciated!

ActiveSheet.PivotTables("PivotTable1").RefreshTable
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Complete Date"). _
        CurrentPage = "(All)"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Complete Date")
        .ClearAllFilters
        For Each ptItem In .PivotItems
            If "2/3/15" = ptItem Then     'I still have the problem when I enter an actual date as opposed to the Now function
                ptItem.Visible = True
            ElseIf  (Now - 2) = ptItem Then
                ptItem.Visible = True
            ElseIf  (Now - 3) = ptItem Then
                ptItem.Visible = True
            Else
                ptItem.Visible = False
            End If
        Next
    End With
End Sub
pnuts
  • 58,317
  • 11
  • 87
  • 139
Julia
  • 11
  • 1
  • It's not very different, but none of those answers applied/worked. Plus, I wasn't sure if something with mine could be not working due to my "Now" function. – Julia Jun 05 '15 at 13:34
  • If you set a breakpoint, do you actually enter the `Visible` part? That is, is this an issue with conditionals or the Pivot Table? I suspect the issue is the conditional, specifically the comparison of a double `Now - XXX` to a String. The one that is a String is not in the "standard" date format for a PivotTable which is `mm/dd/yyyy` for me. Beware that Excel ignores the Number Format when doing the comparison. Might want to `Debug.Print ptItem` to check what is stored. – Byron Wall Jun 05 '15 at 19:35
  • Yes - I changed from "Now" to "Date" and the date is in the proper format. Still haven't been able to figure out the rest though. – Julia Jun 11 '15 at 20:25

0 Answers0