Updating Pivot Table Filter with Date Range VBA

[FarmGuy]

Hello,

New to using VBA and thankful that I came across this forum. I have a pivot table where I am trying to update the Date filter based upon a date range input by the user. I have tried several different variations and have not gotten it to work. Any help or insight would be greatly appreciated. I have attached a simplified sample file that I am certain is a hot mess.

POST MOVED BY MOD

.

 

[Chihiro]

You have Date in "Filters" field, when it is in there you need to filter using PivotItem.Visible state, and you will not be able to use PivotFilters (which is counter intuitive).
Something like below (see attached as well).

Sub PageItemFilter()
Dim pvtF As PivotField
Dim pvtI As PivotItem

Set pvtF = Worksheets("Sample").PivotTables("PivotTable2").PivotFields("Date")
For Each pvtI In pvtF.PivotItems
    If DateValue(pvtI.Name) >= Range("C2").Value2 And DateValue(pvtI.Name) <= Range("C3").Value2 Then
        pvtI.Visible = True
    Else
        pvtI.Visible = False
    End If
Next pvtI

End Sub

Or you can add "Date" into Row Labels field to use your existing code (with some modification) to filter. CLng is used to avoid date format issues.

Sub DateFilter()

'Set the Variables to be used
Dim pt As PivotTable

'Amend here to filter your data
Set pt = Worksheets("PivotTables").PivotTables("PivotTable1")
    pt.PivotFields("Date").ClearAllFilters 'This line is needed to clear existing filter before applying new one
    pt.PivotFields("Date").PivotFilters.Add Type:=xlDateBetween, _
        Value1:=CLng((Range("SDate").Value)), Value2:=CLng((Range("EDate").Value))

End Sub

 

[FarmGuy]

Thank you so much Chihiro!

 

[abz]

You have Date in "Filters" field, when it is in there you need to filter using PivotItem.Visible state, and you will not be able to use PivotFilters (which is counter intuitive).
Something like below (see attached as well).

Sub PageItemFilter()
Dim pvtF As PivotField
Dim pvtI As PivotItem

Set pvtF = Worksheets("Sample").PivotTables("PivotTable2").PivotFields("Date")
For Each pvtI In pvtF.PivotItems
    If DateValue(pvtI.Name) >= Range("C2").Value2 And DateValue(pvtI.Name) <= Range("C3").Value2 Then
        pvtI.Visible = True
    Else
        pvtI.Visible = False
    End If
Next pvtI

End Sub

Or you can add "Date" into Row Labels field to use your existing code (with some modification) to filter. CLng is used to avoid date format issues.

Sub DateFilter()

'Set the Variables to be used
Dim pt As PivotTable

'Amend here to filter your data
Set pt = Worksheets("PivotTables").PivotTables("PivotTable1")
    pt.PivotFields("Date").ClearAllFilters 'This line is needed to clear existing filter before applying new one
    pt.PivotFields("Date").PivotFilters.Add Type:=xlDateBetween, _
        Value1:=CLng((Range("SDate").Value)), Value2:=CLng((Range("EDate").Value))

End Sub

Hi Chihiro

I have a question about your first code to be used with the date in the filters field please..
It works fine as long as there is data present within the date range specified, however when there is no data I am getting an error. Its on this line

Else
pvtI.Visible = False

can you please recommend any workaround to this problem? I don't want to move the date field from filter to either rows or columns section of the pivot.

Any help will be appreciated.

 

[Chihiro]

Depends, what should happen when there's no data for the particular date?

To avoid such issues, I'd recommend use of Timeline slicer if you have Excel 2010 or later.

 

[abz]

if there is no data then ideally I would prefer the pivot to be blank and return no numbers, this is because I am using 'getpivotdata' formula to extract values from the pivot table..

 

[Chihiro]

Unfortunately it isn't possible. Since Pivot Filter requires at least one item to be visible.

 

[CristinaPH]

Hi Chihiro,

My "DATE" in into the into Row Labels so I am using the second code you provided with a modification as I want to make visible only the dates > date defined in cell B2 as dd.mm.yyyy :


Sub DateFilter()

'Set the Variables to be used
Dim pt As PivotTable

'Amend here to filter your data
Set pt = Worksheets("Not existing anymore").PivotTables("ProjectedSales")
pt.PivotFields("Maturity").ClearAllFilters
pt.PivotFields("Maturity").PivotFilters.Add Type:=xlAfter, _
Value1:=CLng((Range("B2").Value))


I am getting run-time error 5: invalid procedure call or argument.

Do you have any idea what is causing the issue?
Thanks!
Cristina

 

[Chihiro]

Upload sample workbook please. Without it, I'll be just guessing as to what's causing that error.

 

[CristinaPH]

sorry, unfortunatelly due to securtiy settings I cannot load the file to external websites. do you have an email adress where I can sent it to you?
Thanks a lot!
Cheers,
Cristina

 

[agosfernandes]

Hi! I've tried the code and worked perfecly if my date's field is in the Lines area of the Pivot Table. However, I need it in the Filters area. Can you help???

>>> use code - tags <<<

Private Sub CommandButton12_Click()
   
    If IsDate(UserForm6.TextBox1.value) And IsDate(UserForm6.TextBox2.value) Then
        ' Gravar a data da TextBox1 na célula A2
        Worksheets("MACRODATA").Range("B1").value = CDate(UserForm6.TextBox1.value)
       
        Worksheets("MACRODATA").Range("B2").value = CDate(UserForm6.TextBox2.value)
    Else
    End If
   
    Dim pt As PivotTable

    Set pt = Worksheets("MACRODATA").PivotTables("Tabela Dinâmica1")
        pt.PivotFields("Data Situação Candidatura").ClearAllFilters 'This line is needed to clear existing filter before applying new one
        pt.PivotFields("Data Situação Candidatura").PivotFilters.Add Type:=xlDateBetween, _
            Value1:=CLng((Range("SDate").value)), Value2:=CLng((Range("EDate").value))
End Sub

 

[vletm]

agosfernandes​

You should open a new thread for Your own case.
Please reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

Especially How to get the Best Results at Chandoo.org