pivotitems.visible problem when its not there

[Jan Grinwis]

Hi,

I do have a problem in my pivot macro.
The macro works great when al the pivotitems are available in the
sheet, but sometimes PM40 (it's an ordertype from SAP Plant
Maintenance)is not available.

Does anyone know a solution to avoid this error, I thought about a
check if the pivot item excist, but don't know how to handle.

Thanks in advance

Jan Grinwis

The error occurs on the line:

'.PivotItems("PM40").Visible = False'

The complete macro looks like this:

Windows("Worksheet in Basis (1)").Activate
Cells.Select
Dim Top10 As PivotTable
Set Top10 = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _
"sheet1!a1:m30000").CreatePivotTable(TableDestination:="")

ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
Top10.SmallGrid = False
With Top10.PivotFields("Omschrijving")
.Orientation = xlRowField
.Position = 1
End With

With Top10.PivotFields("Order")
.Orientation = xlDataField
.Position = 1
End With

With Top10.PivotFields("Invoerdatum")
.Orientation = xlRowField
.Position = 2
End With

With Top10.PivotFields("Ordersoort")
.Orientation = xlColumnField
.Position = 1
End With

With Top10.PivotFields("Ordersoort")
.PivotItems("PM10").Visible = False
.PivotItems("PM30").Visible = False
'.PivotItems("PM40").Visible = False'
.PivotItems("PM50").Visible = False
.PivotItems("PM70").Visible = False
.PivotItems("(blank)").Visible = False
End With

With Top10.PivotFields("Korte tekst")
.Orientation = xlRowField
.Position = 3
End With

Range("B4").Select
Top10.PivotFields("Invoerdatum").Subtotals = _
Array(False, False, False, False, False, False, False, False,
False, False, False, False)
Top10.PivotFields("Invoerdatum").AutoSort _
xlDescending, "Invoerdatum"
Range("A4").Select
Top10.PivotFields("Omschrijving").Subtotals = _
Array(False, False, False, False, False, False, False, False,
False, False, False, False)

With Top10.PivotFields("Omschrijving")
.AutoSort xlDescending, "Count of Order"
.AutoShow xlAutomatic, xlTop, 20, "Count of Order"
End With
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=3
End Sub

 

[Debra Dalgleish]

Add the line:
On Error Resume Next
before that section, and it will skip the line if there is an error.

On Error Resume Next
.PivotItems("PM40").Visible = False