Hi,
This is pivot page filed and i am using Excel 2007.
Also my data contains some blank rows, it also shows (blank) option in pivot page filter. I have also changed the date format for this field to dd-mm-yy, however this is showing some records in the range but not all, like if my date range is from 1 May 2012 to 31 May 2012 and the data contains in sheet is like -- 1 April 2012to 1 June 2012 data and order of data is like 1 may 2012, 2 may, 3 may, 5, 7, 9 May 2012 and so on. Now when i run your vb code this shows the data up to 1 may 2012 to 3 may 2012 only, however this should show all records from 1 may to 31 may 2012. (i think it stops because the date in data is 3 may then 5 may....4 may 2012 is not there..please resolve this)
Note: I have changed some lines in this code: Also this code is working fine for me if there are continuous dates (like 1,2,3,4,5,6, and so on) with no (blank) date....
Sub Test_Filter_Date_Range()
Dim dtFrom As Date, dtTo As Date
Dim PT As PivotTable
With Sheets("Main Menu")
dtFrom = .Range("c3")
dtTo = .Range("e3")
End With
For Each PT In ActiveSheet.PivotTables
mypvt = PT
With Sheets("Vendor Pivots")
Set PT = .PivotTables(mypvt)
MsgBox PT.PageFields.Count
For i = 1 To PT.PageFields.Count
getFld = PT.PageFields(i)
If getFld = "Created Date1" Or getFld = "Joined On1" Or getFld = "Offer Pending1" Or getFld = "Offer Made1" Or getFld = "HAT Test - Schedule1" Then
Exit For
End If
Next
End With
'n1:
Call Filter_PivotField_by_Date_Range( _
PT.PivotFields(getFld), dtFrom, dtTo)
Next
End Sub
Public Function Filter_PivotField_by_Date_Range(pvtField As PivotField, _
dtFrom As Date, dtTo As Date)
Dim bTemp As Boolean, i As Long
Dim dtTemp As Date, sItem1 As String
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With pvtField
.Parent.ManualUpdate = True
For i = 1 To .PivotItems.Count
dtTemp = .PivotItems(i)
bTemp = (dtTemp >= dtFrom) And _
(dtTemp <= dtTo)
If bTemp Then
sItem1 = .PivotItems(i)
Exit For
End If
Next i
If sItem1 = "" Then
MsgBox "No items are within the specified dates."
Exit Function
End If
If .Orientation = xlPageField Then .EnableMultiplePageItems = True
.PivotItems(sItem1).Visible = True
For i = 1 To .PivotItems.Count
If .PivotItems(i).Value = dtFrom Then
If dtTo < dtFrom Then
.PivotItems(i).Visible = False
'MsgBox .PivotItems(i).Value
dtFrom = dtFrom + 1
Else
.PivotItems(i).Visible = True
dtFrom = dtFrom + 1
End If
Else
'MsgBox .PivotItems(i).Value
.PivotItems(i).Visible = False
'dtFrom = dtFrom + 1
End If
Next i
End With
pvtField.Parent.ManualUpdate = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Function
Hi arvindkrkaushik,
Is the field with the dates that you want to filter in the Reports Filter area of the PivotTable or the Row Labels area?
