title: How to update powerpivot pivot table filter via cell reference?
body: <p>I am working on updating a Powerpivot pivot table via a cell reference in a different worksheet but am having trouble with determining the correct syntax.</p>
  
  <p>The code works just fine if I hard-code a date (see below):</p>
  
  <pre><code>Sheets("Close Rate").Select                   'Select the sheet containing the pivot table to update
  
  ActiveSheet.PivotTables("PivotTable1").PivotFields( _
      "[Closed Cases].[Closed Date Week End].[Closed Date Week End]").ClearAllFilters
  
  ActiveSheet.PivotTables("PivotTable1").PivotFields( _
      "[Closed Cases].[Closed Date Week End].[Closed Date Week End]"). _
      CurrentPageName = _
      "[Closed Cases].[Closed Date Week End].&amp;[2013-09-28T00:00:00]"
  </code></pre>
  
  <p>However, if I try to use a variable rather than hard-code a date, I get an "Application Defined or Object Defined error" message.</p>
  
  <p>This is the code I'm trying to use:</p>
  
  <pre><code>'Set up variables
  Dim FilterDate As String
  
  FilterDate = Sheets("CS Dashboard").Range("I5").Value   'Get date for filter
  
  Sheets("Close Rate").Select                   'Select the sheet containing the pivot table to update
  
  ActiveSheet.PivotTables("PivotTable1").PivotFields( _
      "[Closed Cases].[Closed Date Week End].[Closed Date Week End]").ClearAllFilters
  
  ActiveSheet.PivotTables("PivotTable1").PivotFields( _
      "[Closed Cases].[Closed Date Week End].[Closed Date Week End]"). _
      CurrentPageName = _
      "[Closed Cases].[Closed Date Week End].&amp;[FilterDate]"
  </code></pre>
  
  <p>Can anyone give some guidance on how I should code this so that it uses the variable?</p>