Filtering a Pivot by Date Range via VBA

[Excelmad101]

Hi,

I am trying to filter a pivot table using VBA. Specifically a date range. So I want a user to enter a date from and to on a excel sheet and the pivot will then update based on them cells, however I have been unable to thus far.


I have attached an example of my problem.


What I have been able to do so far is filter by 'date' when it is added in as a 'row label' by:

Sub UpdatePivot()

Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Worksheet").PivotTables("PivotTable1")
PvtTbl.ClearAllFilters
PvtTbl.PivotFields("Date").PivotFilters.Add Type:=xlDateBetween, Value1:=CLng(Range("Datefrom").Value), Value2:=CLng(Range("Dateto").Value)

End Sub

-This doesn't work when I have 'date' just in my filter and not row label, as I do not want to do this.

-Please could someone help, I've been trying for a while to fix this now.ExampleofPivot.xlsx

[judgeh59]

I put the start date in cell H1 and end date in cell J1

this code turns all the dates on then turn off the ones that aren't in the date range....

Please Login or Register  to view this content.

[Excelmad101]

Thanks. Done the trick.

A different way to what I was thinking as my original piece of code was pretty close to what I need thought it would have just required some manipulation.

[RyanGumley]

Dang. I was really hoping this bit of code was going to work.

All I get is

"unable to set the visible property of the pivotitem class"