Macro to update most recent x months in pivot table

[yoyoma123]

Hello All,

I have several pivot tables in my workbook with a category "Period" (Date in mm-yyyy format). I only need to see the information for the last 12 months, so every month, when I import new data, I have to go through each pivot table to erase the earliest "Period" and add the most recent one.

I was hoping to write a macro to help me do this task. Is there a way to filter a pivot table category in a macro for the last 12 (or x) months from the previous month(or even today)?

Thanks!

 

[AlphaFrog]

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Period_Last_12_Months()<br><br>    <SPAN style="color:#00007F">Dim</SPAN> pi <SPAN style="color:#00007F">As</SPAN> PivotItem, CutoffDate <SPAN style="color:#00007F">As</SPAN> Date<br>    <br>    CutoffDate = DateAdd("m", -12, Date) <SPAN style="color:#007F00">' 12 months previous from today</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.PivotTables("PivotTable1")<br>        .ManualUpdate = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> .PivotFields("Period")<br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> pi <SPAN style="color:#00007F">In</SPAN> .PivotItems<br>                pi.Visible = DateValue(pi.Value) > CutoffDate<br>            <SPAN style="color:#00007F">Next</SPAN> pi<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        .ManualUpdate = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[yoyoma123]

Thanks for the quick response AlphaFrog!

I tested your code, but I get an "Unable to set the Visible property of the PivotItem class" error.

If I run the macro step by step, it will repeat the Next loop over and over again. Can you help?

 

[yoyoma123]

Actually, I think I've figured it out. My date is stored in 2 columns. "Date" is actually the date in mm/dd/yyyy format. And the Period is just the concatenate of the month and year of the date. If I use your code to filter the "Date" field, then it works.

Thanks!

 

[yoyoma123]

Does this code affect all of the fields in the workbook? After I added your segment to my existing macro, I keep getting errors on the original code that worked before the addition.

Can you help me figure out what is wrong?

    Dim II As String
    II = InputBox("Please Enter Quarter Month(1, 2, 3 or All)", "Quarter Month Selection")
    
    Dim pi As PivotItem, CutoffDate1, CutoffDate2 As Date
    CutoffDate1 = DateAdd("m", -13, Date) ' 12 months previous from today
    CutoffDate2 = DateAdd("m", -6, Date) ' 6 months previous from today

    
    Worksheets("Transactions by In vs Out").Select
    
    Rows("2").Select
    Selection.EntireRow.Hidden = False
    With ActiveSheet.PivotTables("PivotTable2")
        .ManualUpdate = True
        With .PivotFields("Date")
            For Each pi In .PivotItems
                pi.Visible = DateValue(pi.Value) > CutoffDate2
            Next pi
        End With
        .ManualUpdate = False
    End With
    MsgBox ("Part 1 Complete")
    Selection.EntireRow.Hidden = True
    
    Rows("17").Select
    Selection.EntireRow.Hidden = False
        With ActiveSheet.PivotTables("PivotTable1")
        .ManualUpdate = True
        With .PivotFields("Date")
            For Each pi In .PivotItems
                pi.Visible = DateValue(pi.Value) > CutoffDate2
            Next pi
        End With
        .ManualUpdate = False
    End With
    MsgBox ("Part 2 Complete")
    Selection.EntireRow.Hidden = True
       
    ' The macro works great up to here
       
    Worksheets("In-Outsource By Region").Select 'On a new spreadsheet
    
    'Qtr Month is defined as which month in the quarter it is. If the quarter runs from April to June, then April would be the 1st quarter month, May would be 2, June would be 3. etc
    'Based on which number I put in the Inputbox (II), my table should show the Periods that correspond to that Quarter Month. ie. 1 = April, July, October, etc.
    'This code works when the CutOffDate macro is not in place
    
    Rows("2:3").Select
    Selection.EntireRow.Hidden = False
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Qtr Month").CurrentPage = II
    Selection.EntireRow.Hidden = True

I could use two separate macros, but I would like to combine these two commands, because I have a pivot table that is sorted by Quarter Month, and I would like to filter it show the Quarter Months within the last year. i.e. Quarter Month 1: Jul 2010, Oct 2010, Jan 2011, Apr 2011, Jul 2011.

Please help! and Thanks in Advance!