Pivot table filter item doesnt exist

[paulnray]

Hi wonder if anyone can help.

I have a macro that creates 28 workbooks and does pivot tables on each workbook but excludes one of the columns if it is exempt

So I have
A - Recorded
B - Expires This Month
C - Not Recorded
Exempt

Problem is not all of them have Exempt and when this happens it falls over. Is their anyway I can get it to ignore exempt if it doesnt exist.

My code looks like this

Regards

Paul

Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'" & strSheetName & "'!" & strDataRange & "").CreatePivotTable TableDestination:="", _
        TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Care Group")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Cost Centre")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("IPR Recorded")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("IPR Recorded")
        .PivotItems("Exempt").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Assignment"), "Count of Assignment", xlCount
    Range("D13").Select
    ActiveSheet.PivotTables("PivotTable1").NullString = "0"
    ActiveWorkbook.ShowPivotTableFieldList = False

All help appreciated

 

[Andrew Poulsom]

One way:

    On Error Resume Next
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("IPR Recorded")
        .PivotItems("Exempt").Visible = False
    End With
    On Error GoTo 0

 

[paulnray]

Thanks simple code usual works

Regards

Paul