setting date range in VBA to use in Pivot

[Hawjeen]

Hi,

Still very new in VBA, and came across what i could imagine is a very simple problem, with a simple solution.

I need to filter the pivot table to show me the data between the previous thursday and next thursday. All my data is 1 day delayed,
and therefor the use of: today()-1.

so if today is 16 march. i want the range to be 8 march - 15 march.
and of course the same range if today is between that range.

i have tried this below, by recording some macros and combined them, but cant make it work, anyone that can help?

Best regards


Sub Filter_dates()

ActiveSheet.PivotTables("Pivottabel3").PivotFields("Inserat").PivotFilters. _
Add2 Type:=xlDateBetween, Value1:="FormulaR1C1 = (TODAY()-1) - Weekday((TODAY()-1), 2) -3", Value2:="FormulaR1C1 = (TODAY()-1) - Weekday((TODAY()-1), 2) + 4"


End Sub

 

[Hawjeen]

Ive found a workaround with a helper column instead

so need for it anymore

 

[ParamRay]

I did it this way:

Public Sub FilterPivot()
  Dim sht As Worksheet
  Dim pvt As PivotTable
  Dim pvf As PivotField
  
  Set sht = ThisWorkbook.Sheets("Sheet1")  ' <-- set sheet name here
  Set pvt = sht.PivotTables("PivotTable1") ' <-- set pivot table name here
  Set pvf = pvt.PivotFields("Date")        ' <-- set pivot field name here
  
  pvf.ClearAllFilters
  pvf.PivotFilters.Add xlDateBetween, , StartDateString, EndDateString
End Sub

Private Function StartDateString() As String
  StartDateString = Format(Date - 8 - ((Date + 1) Mod 7), "Short Date")
End Function

Private Function EndDateString() As String
  EndDateString = Format(Date - 1 - ((Date + 1) Mod 7), "Short Date")
End Function