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