Filter Pivot Table With VBA

[mouse88]

I have previously used the following code to filter a pivot table by date range:

For Each PivItem In PivTable.PivotFields("Date").PivotItems
    If PivItem.Value < DateFrom Or PivItem.Value > DateTo Then
        If PivItem.Visible = True Then
            PivItem.Visible = False
        End If
    End If
Next PivItem

It worked fine yesterday but today in another workbook I get a data type mismatch error of the line:

If PivItem.Visible = True Then

Any Ideas?

 

[mouse88]

Anyone?

 

[mouse88]

For anyone with this problem which I believe is only in Excel 2010 I used the following line of code to fix it:

PivTable.PivotFields("Date").NumberFormat = "dd/mm/yyyy"

It seems that Excel formats the date in a different way when retreiving the data from the pivot item. In my pivot table it would be formatted as "01/01/2011" but Excel would return the value of "1/1/2011" without the leading zeros.

Problem is this solution has to work in Excel 2007 so im not sure if that has the same problem