hi, I am trying to use code to select dates between a range of dates using the code below. It selects the dates with the dd/mm/yyy format but won't select the ones with just the yyyy format. code is below and file is attached.
Sub PTSelect1()
Dim PvtTbl1 As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim datecriteria1, datecriteria2 As String
datecriteria1 = "1/1/2001"
datecriteria2 = "9/1/2017"
Application.ScreenUpdating = False
Set PvtTbl = Worksheets("orders").PivotTables("PivotTable1")
Set pf = PvtTbl.PivotFields("Need By Period")
' get run-time 1004 error here so leaving out
' pf.NumberFormat = "mm/dd/yyyy"
pf.ClearAllFilters ' clear filters. needs ELSE statement code
' not picking up just the years
For Each pi In pf.PivotItems
If CDate(pi.Value) >= datecriteria1 And CDate(pi.Value) <= datecriteria2 Then
On Error Resume Next
pi.Visible = True
'include ELSE section if you want to clear all filters
Else
pi.Visible = False
End If
Next
Application.ScreenUpdating = True
End Sub
Bookmarks