Filter Pivot table based on max date in column

I am working on a "dashboard" for mangement and want to automate this as much as possible. I have a pile of pivot tables that I need to refresh and filter for the latest date. the source data is on tab "2012 YTD Charges", column A is month (in month/day/year format, 1/1/2012). Each month, a macro will kick off the data import from SQL, and refresh each pivot table. Problem is, how can I set the pivot table report filter to the maximum month value (there are some 7-8k lines of data for each month, all the August data has a date of 8/1/2012 for example). Using macro recording, I found where I can set the .CurrentPage and hard set the value, but I would like to make this dynamic. I can write a formula in Excel to find the max date and put in a hidden sheet, just not sure on the VB side how to get the value into the macro.

Any assistance is greatly appreciated!

Great solution here....thank you! I was actually able to use this same type of solution without adding an additional sheet.

Hi there,
I think this is what I need for my own situation!
Can you please assist in translating what the above code means in relation to my attached sample sheet?

I'd be forever grateful!

Thanks!
Cameron

Well, looking at your spreadsheet, you would need to add to one of the sheets a formula =MAX(DataSource!A:A), if you put it on Sheet2, then you would change the code I wrote as follows (change Sheet8 to Sheet2 or wherever you put it):

Please Login or Register  to view this content.

Note I changed the pivottable name to PivotTable1, which you can find when you click on the Pivot Table and go to Properties I also changed the name of the field to what you are using (YYYY-MM)

Originally Posted by camdameron

Hi there,
I think this is what I need for my own situation!
Can you please assist in translating what the above code means in relation to my attached sample sheet?

I'd be forever grateful!

Thanks!
Cameron

Thanks so much for the prompt response!

I get a RunTime 1004 error:
"Unable to get the PivotTables property of the Worksheet class"

Do you know what this means?

uploading the sheet again with the changes you proposed...

I don't recall which reference it requires for this to work, the ones I have loaded are:
Visual Basic for Applications
Microsoft Excel 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Object Library

Hi I found this thread useful..but I am not sure how to apply to my work
Pivot 1- i need to filter latest and 2nd latest date
Pivot 2- I need to filter latest date

the date always changes (i.e as more data is added, there will be newer date)