[VBA] Filter Pivot Table by dates

Levi92 · #1 11-14-2018, 07:43 PM

I am currently building an application with Excel and would like to add a feature whereby the user can filter a pivot table by simply choosing the date via a user form. Technically speaking, everything works just fine. But here is my problem:

The filter is applied to the pivot table, but the pivot table is blank.

If I go into the filter settings, you can see that VBA correctly enters the dates.

[IMG]www.msofficeforums.com/attachment.php?attachmentid=12893&stc=1&d=15422496 48[/IMG]

Edit: Somehow this picture won't load. Check out the attached file!

If I manually press OK now, the filters are applied successfully:

Here is my VBA Code:

Code:

Sub ApplyDates()

Dim FromDate As Date, ToDate As Date
Dim pvtFilters As PivotFilter

Set wb = ThisWorkbook
Set ws1 = wb.Worksheets(1)
Set wsStaging = wb.Worksheets("Staging_Sheet")

FromDate = Format(wsStaging.Range("FromDate").Value, "MM/DD/YYYY")
ToDate = Format(wsStaging.Range("ToDate").Value, "MM/DD/YYYY")

For Each pvt In ws1.PivotTables
    With pvt
        .ClearAllFilters
        .PivotFields("[Orders].[Day (Value)].[Day (Value)]") _
        .PivotFilters.Add2 Type:=xlCaptionIsBetween, Value1:=FromDate, Value2:=ToDate
    End With
Next

End Sub

Does anyone have an idea how to solve this issue? To me it looks like pivot tables cannot be filtered with VBA

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to filter between two dates in MS Query criteria when the dates are text ("20180903", etc.)	kingmb	Excel	2	09-11-2018 01:08 AM
Grouping Dates in Pivot Table	Dana-H	Excel	1	02-23-2017 12:20 AM
Pivot table dates issue	salse	Excel	1	06-08-2016 12:23 PM
Filter 2 columns between 2 dates	s_samira_21	Excel	2	02-08-2015 10:34 PM
Grouping Dates in Excel Pivot Table	mskennedy	Excel	2	02-27-2013 11:09 AM