If you are working on sales data that keeps on updating daily. To track the complete sales on the latest date entered in the data using pivot table. In this article we will learn how to use pivot table to select newest date in the database using macros.
Following is the snapshot of data we have:
We need follow the below steps:
Dim pfiPivFldItem As PivotItem
Dim dtmDate As Date
dtmDate = Evaluate(“Max(IF(ISNUMBER(“ & .Address(0, 0) & “),” & .Address(0, 0) & “,))”)
For Each pfiPivFldItem In .PivotFields(“Dates”).PivotItems
If pfiPivFldItem.Value = “(blank)” Then
pfiPivFldItem.Visible = False
pfiPivFldItem.Visible = (Cdate(pfiPivFldItem.Value) = CLng(dtmDate))
To work this code dynamically all you need to do is to follow the below steps:
In this way we can select latest date from pivot table using VBA.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.