Excel 2007 Pivot Charts-- Show last 3 months dynamically

[jsolis]

I have a pivot chart that displays the last 3 months of data onto a bar graph (eg, Since it is currently October, the graph shows Jul-Sep data). Instead of selecting the desired months manually , I would like to create a macro that will show the desired months every time the calender month changes.

Please refer me to related forums and any help is much appreciated! THANK YOU!!!

 

[etaf]

you could have an extra column within your data set to use as a page filter which limits the data

for example test for the month - 3

lable the column something like - Months to Show

then assuming the date you want to test is starting in Cell B2,

then use
=B2>=DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY()))

copy down the data set
use a table and it will automatically be inserted when you append new data

then use that column in the page filter and say True

 

[jsolis]

Thanks! No macro even required.

 

[etaf]

no macro , and with a table used as the PT source , it will automatically populate - but there maybe a much better way using dynamic ranges - just not sure - so others may still reply with a better idea

 

[jsolis]

I just recently recorded a macro to see what code is being written in the background. I noticed that every time I selected a certain month in my pivot table, this line of code would appear

With ActiveSheet.PivotTables("PivotTable6").PivotFields("Submit Date")
                 .PivotItems("Nov").Visible = False
                 .PivotItems("Dec").Visible = True
End With

I decided to play around with this line a little bit and was able to select a previous month data by implementing this code.

With ActiveSheet.PivotTables("PivotTable6").PivotFields("Submit Date")
                 .PivotItems("Nov").Visible = False
                 .PivotItems(Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmm")).Visible = True
End With