VBA Pivot table Filter setting Run-time error 5

[Randombard]

Hi All,

This code used to work perfectly but since we moved to excel 2007 it seems to fall over.

it also seems to be completely random as to which call will fail but right now its PT2009

Sub ChangeProduct()
Application.DisplayAlerts = False
Dim J7 As String
Dim J8 As String
Dim J9 As String
Dim J10 As String
Dim J11 As String

J7 = Range("JTest2007")
J8 = Range("JTest2008")
J9 = Range("JTest2009")
J10 = Range("JTest2010")
J11 = Range("JTest2011")
On Error GoTo Catch

2007
If J7 = "#NA" Then GoTo 2008
    Sheets("Sheet1").PivotTables("PT2007").PivotFields("Product").CurrentPage = J7
2008
If J8 = "#NA" Then GoTo 2009
    Sheets("Sheet1").PivotTables("PT2008").PivotFields("Product").CurrentPage = J8
2009
If J9 = "#NA" Then GoTo 2010
    Sheets("Sheet1").PivotTables("PT2009").PivotFields("Product").CurrentPage = J9
2010
If J10 = "#NA" Then GoTo 2011
    Sheets("Sheet1").PivotTables("PT2010").PivotFields("Product").CurrentPage = J10
2011
If J11 = "#NA" Then Exit Sub
    Sheets("Sheet1").PivotTables("PT2011").PivotFields("Product").CurrentPage = J11
    Exit Sub
Catch:
MsgBox "An error has occured please allow the e-mail report to be sent"
Application.DisplayAlerts = False
Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim Who As String
    Dim erval
    erval = Error(Err.Number)
        Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    strbody = "file:///FileName"
    Who = Application.UserName
        On Error Resume Next
    With OutMail
        .To = "my e-mail"
        .CC = ""
        .BCC = ""
        .Subject = Who & "  Error Report  " & erval
        .Body = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub

Error message in full that comes up when stepping through on PT2009:

Run-time error '5':

Invalid Procedure call or argument

Any help appreciated

 

[Randombard]

HI all,

seem to have sorted this the values were still visible in the pivot table but when I went into the field settings on the filter some of the values were hidden.

seems strange that I could still see them when manually selecting but once I un-hid those values in the setting it all worked fine again.