How to Select Latest Date from Pivot Table using VBA in Microsoft Excel?
Tags: toselectlatestdatefr, vbamacros
Published: 2021-09-29 04:56:22

---
title: How to Select Latest Date from Pivot Table using VBA in Microsoft Excel?
published: true
date: 2021-09-26 05:41:04 UTC
tags: ToSelectLatestDateFr,VBAMacros
canonical_url: https://geekexcel.com/how-to-select-latest-date-from-pivot-table-using-vba-in-microsoft-excel/
---

In this blog post, we will guide you on how to ** select the latest date from the pivot table** using VBA in Microsoft Excel. Let’s get them below!! Get an official version of MS Excel from the following link: **[https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)**

## Example

- Firstly, you need to  **create**   **sample data** with  **dates and sales** in an Excel Workbook.

![](https://geekexcel.com/wp-content/uploads/2021/09/Sample-data-34.png)<figcaption>Sample data</figcaption>

- To automatically **find the new date & sales** , you need to make **pivot** by using shortcut key **ALT + N + V**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Pivot-table.png)<figcaption>Pivot table</figcaption>

- In the Excel Worksheet, you have to go to the  **Developer Tab.**
- Then, you need to ** ** select the  **Visual Basic**  option under the  **Code**  section.

![Select Visual Basic](https://geekexcel.com/wp-content/uploads/2021/09/Select-Visual-Basic-9-1024x180.png)<figcaption>Select Visual Basic</figcaption>

- Now, you have to  **copy and paste the code**  given below.

```
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

```

- You need to  **save the code**  by selecting it and then  **close the window**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Save-the-code-71-1024x420.png)<figcaption>Save the Code</figcaption>

- Again, you have to go to the  **Excel Spreadsheet** , and click on the  **Developer Tab**.
- You need to choose the  **Macros option**  in the Code section.

![Choose Macro option](https://geekexcel.com/wp-content/uploads/2021/09/Choose-Macro-option-11-1024x184.png)<figcaption>Choose Macro option</figcaption>

- Now, you have to make sure that your  **macro name is selected**  and click the  **Run ** button.

![](https://geekexcel.com/wp-content/uploads/2021/09/Run-the-Code-42.png)<figcaption>Run the Code</figcaption>

- Or you can **insert a shape** & **assign the macro** , you will get the **latest date**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Insert-Shape.png)<figcaption>Insert Shape</figcaption>

- Finally, to **test the code** , if you want to **change the existing date** , then you will get **output** as given below.

![](https://geekexcel.com/wp-content/uploads/2021/09/Change-the-existing-date.png)<figcaption>Change the existing date</figcaption>

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

- Firstly, you need to press **CTRL + F3 to open Name Manager**.
- In **Refers to box** , you have to **enter the formula** given below and click on **OK button**.

```
=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),2)
```

- Then, you are ready to **use this Named range** in **Pivot table**.
- Now, you need to click on the pivot table.
- From **Analyze ribbon** , you have to click on **Change Data Source**.
- You need to **press F3 shortcut** to launch the existing **Paste Name** and select **LatestDate**.
- After that, you have to click on **OK button twice**.
- To **test,** you can **enter the new date**.
- Finally, all you need to do is **click on the rectangular shape** to run the macro.

## End of the Era

We hope that this tutorial gives you guidelines on how to ** select the latest date from the pivot table** using VBA in Microsoft Excel. Please leave a comment in case of any  **queries,**  and don’t forget to mention your valuable  **suggestions**  as well. Thank you so much for Visiting Our Site!! Continue learning on **[Geek Excel](https://geekexcel.com/)!! **Read more on [**Excel Formulas**](https://geekexcel.com/excel-formula/) **!!**

**Read Next:**

- **[Simple Keyboard Shortcut to Select Entire Pivot Table in Excel 365!!!](https://geekexcel.com/simple-keyboard-shortcut-to-select-entire-pivot-table-in-excel-365/)**
- **[Quick Shortcut to Group Pivot Table Items in Excel 365!!!](https://geekexcel.com/quick-shortcut-to-group-pivot-table-items-in-excel-365/)**
- **[Shortcut to Ungroup Pivot Table Items in Microsoft Excel 365!!!](https://geekexcel.com/shortcut-to-ungroup-pivot-table-items-in-microsoft-excel-365/)**
- **[Shortcut to Hide Pivot Table Items in Microsoft Excel 365!!!](https://geekexcel.com/shortcut-to-hide-pivot-table-items-in-microsoft-excel-365/)**
- **[Simple Shortcut to Open Pivot Table Wizard in Excel 365!!!](https://geekexcel.com/simple-shortcut-to-open-pivot-table-wizard-in-excel-365/)**