How to Select Latest Date from Pivot Table using VBA in Microsoft Excel

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:

• To automatically find the new date & sales, we need to make pivot
• Use shortcut key ALT + N + V

We need follow the below steps:

• Click on Developer tab
• From Code group, select Visual Basic

• Enter the following code in the worksheet module

Sub LatestDatePivot()

    Dim pfiPivFldItem As PivotItem
    Dim dtmDate As Date
    With Worksheets(“Sheet1”).PivotTables(1)
        .PivotCache.Refresh
        .ClearAllFilters
        With .RowRange
            dtmDate = Evaluate(“Max(IF(ISNUMBER(“ & .Address(0, 0) & “),” & .Address(0, 0) & “,))”)
        End With
        For Each pfiPivFldItem In .PivotFields(“Dates”).PivotItems
            If pfiPivFldItem.Value = “(blank)” Then
                pfiPivFldItem.Visible = False
            Else
                pfiPivFldItem.Visible = (Cdate(pfiPivFldItem.Value) = CLng(dtmDate))
            End If
        Next pfiPivFldItem
    End With
End Sub

• After copying the code you need to run the macro by using ALT + F8 & select the macro.
• Or you can insert a shape & assign the macro; we will get the latest date

• To test the code; if we change the existing date; we will get output accordingly

To work this code dynamically all you need to do is to follow the below steps:

• Press CTRL + F3 to open Name Manager
• In Refers to box enter the formula as
• =OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),2)

• Click on ok button
• Now we are ready to use this Named range in Pivot table
• Click on the pivot table
• From Analyze ribbon > Click on Change Data Source

• Press F3 shortcut to launch the existing Paste Name; select LatestDate
• Click on ok button twice

• To test we can enter the new date
• All we need to do is click on the rectangular shape to run the macro.

In this way we can select latest date from pivot table using VBA.

 Download-How to Select Latest Date from Pivot Table using VBA