How to update powerpivot pivot table filter via cell reference?

Question

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.

The code works just fine if I hard-code a date (see below):

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].&[2013-09-28T00:00:00]"

However, if I try to use a variable rather than hard-code a date, I get an "Application Defined or Object Defined error" message.

This is the code I'm trying to use:

'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].&[FilterDate]"

Can anyone give some guidance on how I should code this so that it uses the variable?

score 2 · Accepted Answer · answered Oct 01 '13 at 21:29

2

It's defined as a string but is really a variable. Change this line of code:

"[Closed Cases].[Closed Date Week End].&[" & FilterDate & "]"

answered Oct 01 '13 at 21:29

Automate This

30,726
11
60
82

Modified code but unfortunately still results in same error :( – USG_Phil Oct 01 '13 at 21:43
This, of course, relies on 'I5' being in the correct format. When you debug stop on this line and mouse over your FilterDate variable to see what value it has. Also, what is the cell format of I5? – Automate This Oct 01 '13 at 21:52
Thank you!!! The question on the correct format did it! After slapping myself, I modified the cell to use the same format as the pivot table and it all worked. It's always the simple things ... :) – USG_Phil Oct 01 '13 at 22:06

How to update powerpivot pivot table filter via cell reference?

1 Answers1

Linked