Clearing out old Dates in a Pivot Table

[estrauss22]

I have a pivot table built for "x" number of days. The days change as the year goes along. The data feeding the pivot table changes and has only the most current dates. However, wheneve I refresh the pivot table, every date I've ever had there is displayed. Is there a way to clear out old dates without recreating the entire pivot table?

 

[Datsmart]

Try this code.

Sub Clean_Pivots()
    For Each pt In ActiveSheet.PivotTables
     pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
    Next
End Sub

...Why do old descriptions of OLD data hang around?
Because they're in the domain of PivotField. They're retained so a fixed-dimension PivotTable (with "Show items with no data" checked) can be created; thus, preventing a pre-configured GETPIVOTDATA function from returning #REF! when the data changes.

Above gleaned from this thread:
http://www.mrexcel.com/board2/viewtopic.php?p=1078163

 

[estrauss22]

Where would I load the code and where would I run it? I'm really basic!!!

 

[Datsmart]

Sorry, here are some instructions.

To use the posted code;
Start the Visual Basic Editor (via Menu Tools, Macro, Visual Basic Editor, or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste your code
Note: All Macro code will start with "Sub MacroName()" and End with "End Sub"

How to create a button and assign a macro to it:
If you don't already have the "Forms" toolbar active, click on Tools>Customize>Toolbars and check "Forms". The toolbar will appear on your screen

Click on the Button icon, and drag out a button on the screen. When you release the left mouse button, an "Assign Macro" dialogue box will appear. Highlight whatever macro you want, and click OK. The macro will now be assigned to that button.

You can also run the code by hitting Alt-F8 and selecting the macro name and clicking Run.

 

[estrauss22]

This code has worked from in the past but now when I run it, it brings back the error "Syntax Error" and highlights the following line:

*pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

Any input on how to fix?