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
370 If Not .PivotItems("P1") Then
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

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