Pivot Table Item does not exist, Error 1004 (1 Viewer)

[Kryst51]

I have a spreadsheet where I use vba to create a pivot table, then change the positions of the columns in the pt.

The problem I have is that if the spreadsheet does not contain a specific value it errors (Error 1004).

I found code that claimed to check this and that the method was common, I can't find the site right now, I accidentally closed the browser. But here is my code.... The method doesn't work, and I still get the error, the red portion is the line that errors because that particular Status doesn't exist in the data source for the pivot table.

310       With ActiveSheet.PivotTables("PS_Summary").PivotFields("Priority Status")
320   If .PivotItems("P1A") Is Nothing Then
330     .PivotItems("P1A").Visible = False
340   Else
350     .PivotItems("P1A").Position = 1
360   End If
[COLOR=red][B][U]370   If Not .PivotItems("P1") Then[/U][/B][/COLOR]
380     .PivotItems("P1").Visible = False
390   Else
400     .PivotItems("P1").Position = 2
410   End If
420   If .PivotItems("P2") Is Nothing Then
430      .PivotItems("P2").Visible = False
440   Else
450     .PivotItems("P2").Position = 3
460   End If
470   If .PivotItems("P3") Is Nothing Then
480     .PivotItems("P3").Visible = False
490   Else
500     .PivotItems("P3").Position = 4
510   End If
520   If .PivotItems("P4") Is Nothing Then
530     .PivotItems("P4").Visible = False
540   Else
550     .PivotItems("P4").Position = 5
560   End If
570   If .PivotItems("P5") Is Nothing Then
580     .PivotItems("P5").Visible = False
590   Else
600     .PivotItems("P5").Position = 6
610   End If
620   If .PivotItems("MOH is 2.5 or Greater") Is Nothing Then
630    .PivotItems("MOH is 2.5 or Greater").Visible = False
640   Else
650     .PivotItems("MOH is 2.5 or Greater").Position = 7
660   End If
670   End With

 

[Kryst51]

I ended up removing all the if/then stuff and added to my error handler to resume next when error 1004 is hit.

For posterity's sake it now looks like this:

310       With ActiveSheet.PivotTables("PS_Summary").PivotFields("Priority Status")
320     .PivotItems("P1A").Position = 1
330     .PivotItems("P1").Position = 2
340     .PivotItems("P2").Position = 3
350     .PivotItems("P3").Position = 4
360     .PivotItems("P4").Position = 5
370     .PivotItems("P5").Position = 6
380     .PivotItems("MOH is 2.5 or Greater").Position = 7
390   End With
    'This autfits the columns
400   Cells.EntireColumn.AutoFit
    'This makes sure that the field list is not visible
410   ActiveWorkbook.ShowPivotTableFieldList = False
420   Call FormatPivotTable
MakePivotTable_Exit:
430   Exit Sub
MakePivotTable_Error:
440   Select Case Err
Case 1004 'The particular Priority Status does not exist
450   Resume Next
460   Case Else 'Any Other Error gives the message box
470   MsgBox "Error Line: " & Erl & vbCrLf & "Error: (" & Err.Number & ") " & _
           Err.Description & vbCrLf & "MakePivotTable", vbCritical
480   Resume MakePivotTable_Exit
490   End Select