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:

 

image 1

 

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

image 2

 

We need follow the below steps:

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

image 3

 

  • 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

 

image 4

 

  • 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

image 5

 

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

image 6

 

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)

image 7

 

  • 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

image 8

 

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

image 9

 

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

image 10

 

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

 

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

 

 

Comments

  1. this code is very fantastic but I got the error same thing like VBA learner, I am very need this solving from expert on this group.

    the error in the line of
    pfiPivFldItem.Visible = (CDate(pfiPivFldItem.Value) = CLng(dtmDate))

    Unable to set the Visible property of the PivotItem class

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

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.