title: Macro to automatically select the most recent date in a pivot table filter drop down
body: <p>I have a report that is refreshed each week automatically, and then I have a number of macros that run that do various things. Everything currently works fine, however, I want to write a macro that automatically selects the most recent date in a pivot table. At present, I have the following code:</p>
  
  <pre><code>Sub RefreshPivot()
  '
  ' RefreshPivot Macro
  '
  
  '
      Sheets("Pivot").Select
      ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
      ActiveSheet.PivotTables("PivotTable1").PivotFields("ExtractDate"). _
          ClearAllFilters
      ActiveSheet.PivotTables("PivotTable1").PivotFields("ExtractDate").CurrentPage _
          = "22/08/2017"
  End Sub
  </code></pre>
  
  <p>The date "22/08/2017" is currently the most recent date, but if I run the report next week, the above macro is going to select the 22/08/2017 every time. Is there a quick piece of code that always selects the most recent date in the pivot filter?</p>
  
  <p>Any help would be most welcome.</p>
  
  <p><strong>edit:</strong></p>
  
  <p>So, I've attempted a different solution, that uses a cell reference in my report that contains the most recent date.  I use this date to set the filter criteria, and then the for loop goes through the pivot data and sets each value to blank until it finds a match:</p>
  
  <pre><code>Sub Filter_PivotField()
  'Description: Filter a pivot table for a specific date or period
  
  
  Dim sSheetName As String
  Dim sPivotName As String
  Dim sFieldName As String
  Dim sFilterCrit As String
  Dim pi As PivotItem
  
      'Set the variables
      sSheetName = "Pivot"
      sPivotName = "PivotTable1"
      sFieldName = "ExtractDate"
      'sFilterCrit = "22/08/2017" --most recent date
      sFilterCrit = ThisWorkbook.Worksheets("Pivot").Range("C4").Value
  
      With ThisWorkbook.Worksheets(sSheetName).PivotTables(sPivotName).PivotFields(sFieldName)
          'Clear all filter of the pivotfield
          .ClearAllFilters
  
          'Loop through pivot items of the pivot field
          'Hide or filter out items that do not match the criteria
          For Each pi In .PivotItems
              If pi.Name &lt;&gt; sFilterCrit Then
                  pi.Visible = False
              End If
          Next pi
  
      End With
  
  End Sub
  </code></pre>
  
  <p>However, when I run this, I get the following error:</p>
  
  <blockquote>
    <p>Run-time error '1004': Unable to set the Visible property of the PivotItem class.</p>
  </blockquote>
  
  <p>Is anyone able to assist me?  </p>