Alter Pivot Table Filters with VBA

[KrisBu]

This is my first post on the forum so greetings

Sadly I come with a puzzle:

I have a Pivot table with 3 fields in the Reports Filter. These filters are determined in cells B1, B2 and B3. B2 & B3 are strings and B1 is a date value.

If I select (manually) from any field filter list or type a value in (providing its within the data); it updates the pivot table data correctly.

Now within VBA; I can change the string filtered cells B2 & B3 without issue but struggle with B1.

I want to select a date:

Dim oDate as Date
oDate = "08/06/2011"
Worksheets("PT - Pops").Range("B1").Value = oDate

When I run the VBA; I get an excel error:
"No item of this name exists in the PivotTable report. Rename '09/06/2011' to '40702'

I've tried changing oDate to string/variant but still get the same error message.

Any ideas what I'm doing wrong here?

 

[Desu Nota from Columbus]

Welcome to the forum! I have found it to be a life-saver.

08/06/2011 = 40702

Excel sees dates as serial numbers. While we see August 6th, 2011, excel is crunching the number 40702--Go to the data in the worksheet and look at the sample window while trying to change the format.

This allows us the ability to manipulate dates in formulas and formats, yada yada yada...

Instead of saying

oDate = "08/06/2011"
Worksheets("PT - Pops").Range("B1").Value = oDate

You specify oDate twice?

Have you tried only using:

oDate = Worksheets("PT - Pops").Range("B1").Value

Dim oDate as Date
oDate = Worksheets("PT - Pops").Range("B1").Value

 

[KrisBu]

Yeah I realise a date value is actually that number but the pivottable (seemingly) won't accept the date or a text value by code.

My example shows me assigning a valid date (Its an example but instead of saying odate = 08/06/2011; the final code would point to a value on another sheet) as either a date or string object and assigning this date or string object to the range B1 on the sheet that the pivot table is filtered by.

Therefore; I'd like it to filter the pivottable by date on '08/06/2011'. However regardless of passing a string or date to that cell - I get the error message.