Macro Pivot Table Issue

[rbyrne8760]

Hello,

I am creating a macro that creates several pivot tables. These tables each filter out a set of data. If the report I run the macro on, one day, has no results for one of those sets of data, the macro results in an error. The error states "Unable to set visible property of PivotItem class" in Excel 2007. When I go to debug this, I am brought to this string of information.

    ActiveSheet.PivotTables("PivotTable5").PivotFields("Repair Unit").CurrentPage _
        = "(All)"
    'With ActiveSheet.PivotTables("PivotTable5").PivotFields("Repair Unit")
     '   .PivotItems("CAMERAS").Visible = False
      '  .PivotItems("HENKE").Visible = False
       ' .PivotItems("INSIDE REPAIR").Visible = False
        '.PivotItems("OEM REPAIR").Visible = False
    
     Set PF = ActiveSheet.PivotTables("PivotTable5") _
        .PivotFields("Repair Unit")
    
    PF.ClearAllFilters
    
    With PF
        For Each PI In .PivotItems
            If Not (PI.Caption Like "OUTSIDE REPAIR") Then
                PI.Visible = False
            End If
        Next PI
    
    End With

Can anyone help me with this? If there are no results to filter out for that day, I simply want the results box for that category to show up blank

 

[Zack Barresse]

Hi there, welcome to the board!

I would use a function to determine if the field exists, like so..

Function FIELDHASDATA(ByVal Field As PivotField) As Boolean
    On Error Resume Next
    FIELDHASDATA = CBool(Field.DataRange.Value = "(blank)")
    On Error GoTo 0
End Function

Then you just call it in your routine in an If/Then clause, like so...

Sub TEST()
    On Error Resume Next
    If FIELDHASDATA(ActiveSheet.PivotTables("PivotTable1").PivotFields("Repair Unit")) = True Then
        'do stuff to the field here
    End If
    On Error GoTo 0
End Sub

Notice the error handling in the second routine, because if the field doesn't exist it will throw an error.

HTH

 

[rbyrne8760]

Hello Zack,

Thanks for the function and if/then clause! However, when I insert the function code, it wants me to insert an end sub, thus creating two different macros in the middle of mine. Is there a way I can avoid this? Thanks!

 

[Zack Barresse]

You're probably calling it wrong. Do it like I posted, or post your code so I can see what's going on.