VBA Pivot Table: Error If cannot find - How to ignore

[jamescooper]

Hello, have the following code, and when it cannot find #N/A in the pivotfield it returns 1004 error code, is there anyway I can adapt the code to ignore it if this is the case?

Many thanks.

Sub Pivot_Table()


Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String
Dim PF As PivotField
Dim PI As PivotItem


Application.Calculation = xlManual


Last_Row = Sheets("Calculations").Range("A" & Rows.Count).End(xlUp).Row


'Determine the data range you want to pivot
  SrcData = ActiveSheet.Name & "!" & Range("A2:AE" & Last_Row).Address(ReferenceStyle:=xlR1C1)


'Create a new worksheet
  Set sht = Sheets.Add


'Where do you want Pivot Table to start?
  StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)


'Create Pivot Cache from Source Data
  Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=SrcData)


'Create Pivot table from Pivot Cache
  Set pvt = pvtCache.CreatePivotTable( _
    TableDestination:=StartPvt, _
    TableName:="PivotTable1")
        
pvt.ManualUpdate = False
        
Set pvt = ActiveSheet.PivotTables("PivotTable1")
    
  'Add item to the Report Filter
    pvt.PivotFields("Point 1").Orientation = xlRowField
    pvt.PivotFields("Point 1 Stanox Code").Orientation = xlRowField
    pvt.PivotFields("Point 2").Orientation = xlRowField
    pvt.PivotFields("Point 2 Stanox Code").Orientation = xlRowField
    pvt.PivotFields("Mileage").Orientation = xlRowField
    pvt.RowAxisLayout xlTabularRow
    pvt.ColumnGrand = False
    pvt.RowGrand = False
    pvt.RepeatAllLabels xlRepeatLabels
    pvt.PivotFields("Point 1").Subtotals(1) = False
    pvt.PivotFields("Point 2").Subtotals(1) = False
    
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Mileage")
        .PivotItems("#N/A").Visible = True
  
        For Each PI In .PivotItems
            If PI.Name <> "#N/A" Then
                PI.Visible = False
            End If
        Next
  
    End With

End Sub

 

[mole999]

did you try

On Error Resume Next

 

[codeliftsleep]

Either as stated above, use on error resume next, or you can check to see if there are any #N/A values before running code