Change pivot page filter based upon two cell values (dates from and to)

Victor70 · Jan 18, 2012

Dear experts,

I saw solutions to update the pivot page filter based upon a cell value via a macro (see the link).
http://www.excelforum.com/excel-prog...ell-value.html

Is there a way to apply a from-to criteria for the pivot page filter to get the data for specific time interval? I was unable to modify the referenced code being unsure if pivot page filter supports complex criteria at all.

Thanks a lot for any thoughts.

This duplicates the same question at http://www.excelforum.com/excel-gen...d-upon-two-cell-values-dates-from-and-to.html No responses there so far...

arvindkrkaushik · Jun 15, 2012

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

JS411 said:
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?

Jerry Sullivan · Jun 15, 2012

The problem might be that a text comparison is happening instead of a date comparison.

Before addresssing that though, please clarify what you are trying to do with this part of the code.

Code:

    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

Are you trying to apply the date filters to all pivottables on Sheet "Vendor Pivots"?

Is the PageField with the Dates to be filtered named differently in each Pivot- so you are trying to find the correct field in each PivotTable?

arvindkrkaushik · Jun 15, 2012

Hi

Thank u so much for quick response...

You are correct, there are 8 Pivot tables in vendor pivots sheets and I want to select dates in each pivot...like below example....

[TABLE="width: 3421"]
<TBODY>[TR]
[TD]Created Date1[/TD]
[TD](All)[/TD]
[TD][/TD]
[TD][/TD]
[TD]Offer Made1[/TD]
[TD](All)[/TD]
[TD][/TD]
[TD]Joined On1[/TD]
[TD](All)[/TD]
[TD="align: left"]

$C:\DOCUME~1\ARVIND~1.KAU\LOCALS~1\Temp\msohtmlclip1\01\clip_image001.emz$

False Pict

[/TD]
[TD]Created Date1[/TD]
[TD](Multiple Items)[/TD]
[TD][/TD]
[TD]Offer Pending1[/TD]
[TD](Multiple Items)[/TD]
[/TR]
[TR]
[TD]Vendor Vs Employee Referral1[/TD]
[TD]Vendor[/TD]
[TD][/TD]
[TD][/TD]
[TD]Vendor Vs Employee Referral1[/TD]
[TD]Vendor[/TD]
[TD][/TD]
[TD]Vendor Vs Employee Referral1[/TD]
[TD]Vendor[/TD]
[TD][/TD]
[TD]Vendor Vs Employee Referral1[/TD]
[TD]Vendor[/TD]
[TD][/TD]
[TD]Vendor Vs Employee Referral1[/TD]
[TD]Vendor[/TD]
[/TR]
[TR]
[TD]Stage[/TD]
[TD](Multiple Items)[/TD]
[TD][/TD]
[TD][/TD]
[TD]Client[/TD]
[TD](All)[/TD]
[TD][/TD]
[TD]Status[/TD]
[TD]Joined[/TD]
[TD][/TD]
[TD]Stage[/TD]
[TD]Offer[/TD]
[TD][/TD]
[TD]Client[/TD]
[TD](All)[/TD]
[/TR]
[TR]
[TD]Status[/TD]
[TD](Multiple Items)[/TD]
[TD][/TD]
[TD][/TD]
[TD]Agency1[/TD]
[TD](All)[/TD]
[TD][/TD]
[TD]Client[/TD]
[TD](All)[/TD]
[TD][/TD]
[TD]Status[/TD]
[TD](Multiple Items)[/TD]
[TD]`[/TD]
[TD]Agency1[/TD]
[TD](All)[/TD]
[/TR]
[TR]
[TD]Client[/TD]
[TD](All)[/TD]
[TD][/TD]
[TD][/TD]
[TD]Location[/TD]
[TD](All)[/TD]
[TD][/TD]
[TD]Agency1[/TD]
[TD](All)[/TD]
[TD][/TD]
[TD]Client[/TD]
[TD](All)[/TD]
[TD][/TD]
[TD]Location[/TD]
[TD](All)[/TD]
[/TR]
[TR]
[TD]Agency1[/TD]
[TD](All)[/TD]
[TD][/TD]
[TD][/TD]
[TD]Offered[/TD]
[TD][/TD]
[TD][/TD]
[TD]Location[/TD]
[TD](All)[/TD]
[TD][/TD]
[TD]Agency1[/TD]
[TD](All)[/TD]
[TD][/TD]
[TD]Selected Candidates[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Location[/TD]
[TD](All)[/TD]
[TD][/TD]
[TD][/TD]
[TD]Row Labels[/TD]
[TD]Count of Candidate Name[/TD]
[TD][/TD]
[TD]Joined On[/TD]
[TD][/TD]
[TD][/TD]
[TD]Location[/TD]
[TD](All)[/TD]
[TD][/TD]
[TD]Row Labels[/TD]
[TD]Count of Candidate Name[/TD]
[/TR]
[TR]
[TD]Interview Attended[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]<--None-->[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD]Row Labels[/TD]
[TD]Count of Candidate Name[/TD]
[TD][/TD]
[TD]Offer Rejected[/TD]
[TD][/TD]
[TD][/TD]
[TD]2COMS Consulting - 1100007467[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]Count of Candidate Name[/TD]
[TD][/TD]
[TD][/TD]
[TD]2COMS Consulting - 1100007467[/TD]
[TD="align: right"]310[/TD]
[TD][/TD]
[TD]2COMS Consulting - 1100007467[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]Row Labels[/TD]
[TD]Count of Candidate Name[/TD]
[TD][/TD]
[TD]5 Elements Techno Services (P) Ltd - 1100007698[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]<--None-->[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[TD]5 Elements Techno Services (P) Ltd - 1100007698[/TD]
[TD="align: right"]271[/TD]
[TD][/TD]
[TD]5 Elements Techno Services (P) Ltd - 1100007698[/TD]
[TD="align: right"]35[/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD][/TD]
[TD][/TD]
[TD]Abacus Consultants - 1100003961[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]2COMS Consulting - 1100007467[/TD]
[TD="align: right"]156[/TD]
[TD][/TD]
[TD][/TD]
[TD]9.9 EXPERTS - 1100007481[/TD]
[TD="align: right"]41[/TD]
[TD][/TD]
[TD]9.9 EXPERTS - 1100007481[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ABC Consultants - 1100007039[/TD]
[TD="align: right"]1[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]

Please let me know if u need anything else.....if u want i can send u my excel file but don't know how to send you.

Thanks,
Arvind Kaushik

JS411 said:
The problem might be that a text comparison is happening instead of a date comparison.

Before addresssing that though, please clarify what you are trying to do with this part of the code.

Code:

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

Are you trying to apply the date filters to all pivottables on Sheet "Vendor Pivots"?

Is the PageField with the Dates to be filtered named differently in each Pivot- so you are trying to find the correct field in each PivotTable?

arvindkrkaushik · Jun 15, 2012

Hi,

Did u receive the file......

Thanks,
Arvind Kaushik

Jerry Sullivan · Jun 16, 2012

Arvind,

I reviewed your file and the code you were trying. You made several changes to the Filter_PivotField_by_Date_Range function that was shown in post #2 of this thread that made the function not work as intended.

Try replacing the function and calling sub with this code.

Code:

Sub Test_Filter_Date_Range()
    Dim dtFrom As Date, dtTo As Date
    Dim vFields As Variant
    Dim PT As PivotTable
    Dim bFound As Boolean
    
    With Sheets("Main Menu")
        dtFrom = .Range("C3")
        dtTo = .Range("E3")
    End With
    Application.ScreenUpdating = False
    
    vFields = Array("Created Date1", "Joined On1", _
        "Offer Pending1", "Offer Made1", "HAT Test - Schedule1")
        
    For Each PT In Sheets("Vendor Pivots").PivotTables
        bFound = False
        With PT
            For i = 1 To .PageFields.Count
                bFound = Not IsError(Application.Match(.PageFields(i), _
                    vFields, 0))
                If bFound Then
                    Call Filter_PivotField_by_Date_Range( _
                        .PageFields(i), dtFrom, dtTo)
                    Exit For
                End If
            Next
        End With
    Next
    Application.ScreenUpdating = True
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.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
            Exit Function
        End If
        If .Orientation = xlPageField Then .EnableMultiplePageItems = True
        For i = 1 To .PivotItems.Count
            If (.PivotItems(i) <= dtTo And .PivotItems(i) >= dtFrom) _
                <> .PivotItems(i).Visible Then _
                .PivotItems(i).Visible = Not .PivotItems(i).Visible
        Next i
    End With
     
    pvtField.Parent.ManualUpdate = False
    Application.Calculation = xlCalculationAutomatic
End Function

One other problem in your file is that your Pivots were saved without the underlying data. This prevents the filters from being changed to make Hidden items Visible.

Use these steps to change that setting:
Right-click on Pivot Table > PivotTable Options > Data >
Click checkbox "Save source data with file"

arvindkrkaushik · Jun 16, 2012

Hi Jerry,

Thanks for this information!!!!!!, but I have done as per your instructions but none of these is working for me. I am trying to change the date from 1 May 2012 to 30 May 2012 but this is not showing the correct results. Please help me on this.

Thanks,
Arvind Kaushik

JS411 said:

Arvind,

I reviewed your file and the code you were trying. You made several changes to the Filter_PivotField_by_Date_Range function that was shown in post #2 of this thread that made the function not work as intended.

Try replacing the function and calling sub with this code.

Code:

Sub Test_Filter_Date_Range()
    Dim dtFrom As Date, dtTo As Date
    Dim vFields As Variant
    Dim PT As PivotTable
    Dim bFound As Boolean
    
    With Sheets("Main Menu")
        dtFrom = .Range("C3")
        dtTo = .Range("E3")
    End With
    Application.ScreenUpdating = False
    
    vFields = Array("Created Date1", "Joined On1", _
        "Offer Pending1", "Offer Made1", "HAT Test - Schedule1")
        
    For Each PT In Sheets("Vendor Pivots").PivotTables
        bFound = False
        With PT
            For i = 1 To .PageFields.Count
                bFound = Not IsError(Application.Match(.PageFields(i), _
                    vFields, 0))
                If bFound Then
                    Call Filter_PivotField_by_Date_Range( _
                        .PageFields(i), dtFrom, dtTo)
                    Exit For
                End If
            Next
        End With
    Next
    Application.ScreenUpdating = True
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.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
            Exit Function
        End If
        If .Orientation = xlPageField Then .EnableMultiplePageItems = True
        For i = 1 To .PivotItems.Count
            If (.PivotItems(i) <= dtTo And .PivotItems(i) >= dtFrom) _
                <> .PivotItems(i).Visible Then _
                .PivotItems(i).Visible = Not .PivotItems(i).Visible
        Next i
    End With
     
    pvtField.Parent.ManualUpdate = False
    Application.Calculation = xlCalculationAutomatic
End Function

One other problem in your file is that your Pivots were saved without the underlying data. This prevents the filters from being changed to make Hidden items Visible.

Use these steps to change that setting:
Right-click on Pivot Table > PivotTable Options > Data >
Click checkbox "Save source data with file"

Jerry Sullivan · Jun 16, 2012

Did it work for you in the file that I sent to you?

If not, what happened when you ran the macro in that file?

arvindkrkaushik · Jun 16, 2012

Hi,

The file you sent me..I just changed the date from 1 May 2012 to 30 May 2012 and ran the macro but this was showing records which were previously shown then I select all dates in each pivot tables and again run the program but this time it was showing all records...I don't know what is the problem. Jerry please test this program once again for me and please select dates from 1 May 2012 to 30 May 2012. Please resolve my problem..Many thanks in advance...

Thanks,
Arvind Kaushik

JS411 said:
Did it work for you in the file that I sent to you?

If not, what happened when you ran the macro in that file?

Jerry Sullivan · Jun 17, 2012

I'm not having the problem you describe.

The file that I sent to you has dates 1 April 2012 to 30 May 2012.
When I change the from date to 1 May 2012, the macro works as desired and just shows May dates.
When I manually clear all filters to Show All then run the macro again, it filters to just show May dates.

Try going back to the original file attached to my email and running those tests.
If it still doesn't work, that would imply it's a difference in the computer environments - perhaps Regional Settings affecting the way dates are compared.
If that's the problem, we should be able to add some conversion functions to ensure the dates are being compared as numbers.

arvindkrkaushik · Jun 17, 2012

Jerry,

I have already re-downloaded the file you sent me but still I am facing the same problem. Also I am using Execel Version 2007, And My regional setting date format is : dd-mmm-yy and which I can not change as other applications are using this format. So please suggest me other way so that this macro could work fine for me.

Note: I also ran your macro by debuging step by step and this is also comparing the correct date formats but still pivots are not showing correct values.

Sorry for bothering you again and again.

Thanks,
Arvind Kaushik

JS411 said:
I'm not having the problem you describe.

The file that I sent to you has dates 1 April 2012 to 30 May 2012.
When I change the from date to 1 May 2012, the macro works as desired and just shows May dates.
When I manually clear all filters to Show All then run the macro again, it filters to just show May dates.

Try going back to the original file attached to my email and running those tests.
If it still doesn't work, that would imply it's a difference in the computer environments - perhaps Regional Settings affecting the way dates are compared.
If that's the problem, we should be able to add some conversion functions to ensure the dates are being compared as numbers.

Change pivot page filter based upon two cell values (dates from and to)

Victor70

New Member

arvindkrkaushik

New Member

Excel Facts

Jerry Sullivan

MrExcel MVP

arvindkrkaushik

New Member

arvindkrkaushik

New Member

Jerry Sullivan

MrExcel MVP

arvindkrkaushik

New Member

Jerry Sullivan

MrExcel MVP

arvindkrkaushik

New Member

Jerry Sullivan

MrExcel MVP

arvindkrkaushik

New Member

Similar threads

Forum statistics

Share this page

Change pivot page filter based upon two cell values (dates from and to)

New Member

New Member

Excel Facts

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock