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:-

Please Login or Register  to view this content.

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:-

Please Login or Register  to view this content.