Excel VB Code to Set Filter to a range of Options

Question

I currently have a Pivot Table that pulls its data from a SharePoint site. With this Pivot table I want to use a Button to set the Filter to a certain date range. I used this site to get the filter to change the Filter value to a certain date, code below:

Sheets("Sheet2").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").CurrentPage = Date

This works well to reset the filter to the current days date, but I can't seem to find any way to make it set the filter to all the dates for the past 5, 10, or 15 days, or even to set the filter to all the dates for the current month.

Any assistance would be greatly appreciated.

Answer 1

Given that you are trying to apply a date range to your ReportFilter (as evidenced with .CurrentPage = Date, you will need to loop through all the PivotItems in the Date PivotFields and test against your date range.

Something like this below (untested):

Dim ws as Worksheet, pt as PivotTable, pf as PivotField, pi as PivotItem
Dim dStart as Date, dEnd as Date

dStart = #1/1/2015#
dEnd = #1/31/2015#

Set ws = Sheets("Sheet2")
Set pt = ws.PivotTables("PivotTable1")
Set pf = pt.PivotFields("Date")

With pf
   .ClearAllFilters
   For each pi in pf.PivotItems
      If pi.Value > = dStart And pi.Value <= dEnd Then 
         pi.Visible = True 'or maybe pi.Selected = True
      Else
         pi.Visible = False 'or maybe pi.Selected = False
      End If
   Next
End With