Latest Date required in Pivot Data Filter

[PGJS]

Hello, my name is Paul and a novice in VBA code. I have used recording macros and the internet to get by but hoping to improve my VBA by being part of Chandoo. I came across this site when searching for a solution to a problem similiar to mine named "VBA code to select the latest date from a pivot table field" post.

I have created a mock up of my problem - see attached.

Basically I want the pivot filter seen in cell G2 to be automatically be set to the latest date which in this case would be the 03/08/2014 as part of a macro run each week. I have deliberately created a blank row so it shows as up as (blank) in the pivot filter.

Each week more data is added (through a macro) and I need a VBA method to refresh the pivot table and set the filter to the latest date. Note: in the mock up the pivot source data is set just for the data range, normally this would be set for the column ranges A:C

Hope you can help, cheers Paul

 

[Hui]

Paul

Firstly, Welcome to the Chandoo.org Forums

I would use two small pieces of VBA Code

In the Workbook Module

Private Sub Workbook_Open()
  Set_Filter_Date
End Sub

Then in a standard code module

Sub Set_Filter_Date()
Dim myDate As Date
  myDate = Application.Max([myData])
 
  ActiveSheet.PivotTables("PivotTable1").PivotFields("Report Date"). _
  ClearAllFilters
  ActiveSheet.PivotTables("PivotTable1").PivotFields("Report Date").CurrentPage _
  = myDate
End Sub

Then save the file as a *.xlsm file type

See attached file:

I saved the file with 13/7 as the date


Watch it change when you open the file

 

[PGJS]

Thanks Hui

I have implemented your code and it works perfectly. I actually understand what the code is doing.

The .CurrentPage could have been better named .CurrentPivotFilterItem or perhaps it has other uses.

Thanks again, really appreciated, Paul

 

[Hui]

.CurrentPage is appropriate as there are also Row and Column Filters which are used in Pivot tables

It is a member of .PivotFileds as are Rows and Columns

 

[PGJS]

Thanks for the clarification and quick service.

cheers Paul