Macro to update pivot item (date format) in pivot table to latest date from data source.

Hi everyone, I need help in creating macro that will update pivot items in my pivot table which are in date format to the latest date from the datasource. I have attached sample file as well.

problemsample.xlsx

In the sample file in sheets "pivot" i have the pivot table with concerned pivot Items in date format highlighted in different colours. The macro I should update the pivot items to latest dates according to the colour extracting data from datasource which is updated daily.

Yellow means the macro should update the date to the latest date in the datasource.
Blue, the macro should update to a date one day before yellow.
Orange, date a week before yellow.

The problem should be simple enough but I cant find a way yet to automate it in macro. It should involve manipulation of the pivot Items in the pivot table using VBA.

Appreciate it if anyone could help, Thanks.

Ive written a code that partially does what I want:-

Sub ShowSpecificDates()

Dim pt As PivotTable
Dim pi As PivotItem
Dim i As Integer

Set pt = Sheets("pivot").PivotTables("PivotTable1")

pt.PivotFields("Date").ClearAllFilters
pt.PivotFields("Date").AutoSort xlDescending, "Date"

'code that hides all pivotitems

pt.PivotFields("Date").PivotItems(i + 1).Visible = True
pt.PivotFields("Date").PivotItems(i + 2).Visible = True
pt.PivotFields("Date").PivotItems(i + 6).Visible = True

End Sub

However there is an extra column which is the last date in descending order (i + lastnumber), can anyone point me how to get/define the last integer (i + #)?

Just to bring closure to this thread, i've found the solution myself. Basically I make the pivot items refer to a cell in the worksheet, the cell holds a declared variable value.

Code is like this:-

Sub ShowAllVariance()

Dim pt As PivotTable
Dim i As Integer
Dim fdate As String
Dim fdate1 As String
Dim fdate3 As String
Dim fdate7 As String

Application.ScreenUpdating = False
Application.Calculation = xlManual

Set pt = Sheets("Mysheet").PivotTables("PivotTable1")

pt.PivotCache.Refresh

pt.PivotFields("Date").AutoSort xlDescending, "Date"

ClearItems 'macro to hide all pivot items

fdate = Sheets("Mysheet").Cells(10, 4).Value
fdate1 = Sheets("Mysheet").Cells(10, 4).Value - 1
fdate3 = Sheets("Mysheet").Cells(10, 4).Value - 3
fdate7 = Sheets("Mysheet").Cells(10, 4).Value - 7

pt.PivotFields("Date").PivotItems(fdate).Visible = True
pt.PivotFields("Date").PivotItems(fdate1).Visible = True
pt.PivotFields("Date").PivotItems(fdate3).Visible = True
pt.PivotFields("Date").PivotItems(fdate7).Visible = True

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

End Sub