Pivot Filter Macro - Auto Update to select previous 30 days.

Hi There,

Let me start by thanking everyone in advance for their suggestions. At work we have several reports to fill out on a daily basis; we have a pivot table that pulls all the info we could possibly want from a server location so ultimately everyone just needs to manipulate the table to show their specific report. In the interest of making it totally fool proof I've created macros that automatically fill out everyone's individual reports. All the pivot table have Dates in the row labels with each row representing 1 day in the format MM/DD/YYYY. Here's the one part I haven't been able to work out myself; the reports are only supposed to include the previous 30 days not including today and I haven't quite worked out how to code that. What I would like is for someone to provide some code that can determine what dates fall into the previous 30 days (not including today) and auto select those dates within my field filter. Unfortunately I can't attach a sample doc since excel is pulling the data from a network location but if this isn't clear enough I'll create a sample doc with dummy data.

Any help will be greatly appreciated! Thanks again!

Originally Posted by :) Sixthsense :)

If possible you can allot a helper column in your source data and use formula like the below to determine the required data to get displayed in pivot and use that column in your pivot for filtering data.

=IF(AND(A1>=(TODAY()-30),A1<=TODAY()),"Yes","")

Hey Sixth Sense, thanks for your reply that's a good idea. Unfortunately, I don't have access to the source data since it's all contained on a server and is uploaded automatically. I'll talk to my IT guys and see if we can implement an extra field like this. In the meantime is there anyone that can suggest a method that doesn't involve modifying the source data?