I am using2003 on Windows XP. This macro works for me. 'Option Explicit' is set at the beginning of the module.
Sub Set_Filters() ' ' Set_Filters Macro '
Dim pf As PivotField Dim i As Integer Dim MaxAsOf As String Set pf = ActiveSheet.PivotTables(1).PivotFields("RunType") pf.CurrentPage = "Production" Set pf = Nothing Set pf = ActiveSheet.PivotTables(1).PivotFields("AsOf") MaxAsOf = pf.PivotItems(1) For i = 1 To pf.PivotItems.Count If pf.PivotItems(i).Visible Then If DateValue(pf.PivotItems(i).Value) > DateValue(MaxAsOf) Then MaxAsOf = pf.PivotItems(i).Value End If End If Next i If pf.VisibleItems.Count = 1 Then pf.CurrentPage = MaxAsOf End If Set pf = Nothing
On my tester's machine,2007 on Windows XP, it yields "runtime error 13 type mismatch" for the line...
If pf.PivotItems(i).Visible Then
I've checked the ObjectBrowser in Excel 2003 and find that Visible is a valid property of PivotItem. Going on the assumption that the mismatched thing was the variable "i" I looked at the Locals and Watches windows while stepping through the macro saw that it was type variant. I "Dim"-ensioned it as an integer and put it back to my tester and got the same error. My question is, first, am I looking at the correct suspect for the mismatch, and second, what type does it need to be to index the PivotItem? Thank you for any light you can shed on this.
I am not sure whytowould make a difference, and I will acknowledge not feeling sure at all as to how to answer this. It's slightly beyond my expertise.
but in trying to, have you tried change the line to this:
If pf.PivotItems(i).Visible = True Then