Unable to get the PivotItem property of the PivotField Class error

While waiting, since there maybe a point where the table would not capture this field (if it did not exist in raw data) I designed a code to add the field

The error is on this line now

   If Not (.PivotItems("R50") Is Nothing) Then

Select allOpen in new window

of this block of code

With ActiveSheet.PivotTables("PPMain").PivotFields("SubBill No")
    If Not (.PivotItems("R50") Is Nothing) Then
        .PivotItems("R50").Visible = False
    End If
End With

Select allOpen in new window

Sample.xls

PS - you can also enumerate all pivotTables,pivotFields,pivotItems with code like this (e.g., if you want to store into an array, or do some clearance method, or etc...)

You could build a "does pivot item" exists using enumeration , where you go through the list and compare against the item returning True/False...

At any rate, here's the enumerate code:

Sub EnumeratePivotTables()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim pf As Variant

Application.ScreenUpdating = False

   
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            For Each pf In pt.PageFields
                For Each pi In pf.PivotItems
                    Debug.Print "Sheet: " & ws.Name & ", PivotTable: " & pt.Name & ", PageField: " & pf.Name & ", PivotItem: " & pi.Name
                Next pi
            Next pf
        Next pt
    Next ws
End Sub

Here's that UDF I was talking about = something like:

 

Function pivotItemExists(pfSearch As String, pf As PivotField) As Boolean
Dim pi As PivotItem

    For Each pi In pf.PivotItems
        If pi.Name = pfSearch Then
            pivotItemExists = True
            Exit Function
        End If
    Next pi
    pivotItemExists = False
    
End Function

Select allOpen in new window

and in your code, working like:
 

Sub copy()
Dim mystr As String, pt As PivotTable, tblRng As Range
  
Set ulist = Sheets("Paste Ulist")
Set mp = Sheets("Main Page")

ulist.Activate

Range("G1").Activate

If (Cells(3, 7).Value <> "SubBill No") Then
 
     ActiveCell.EntireColumn.Insert
     Range("G3").Value = "SubBill No"
End If

mp.Activate
      
Set tblRng = ulist.Range("A3:S" & ulist.Range("A" & ulist.Rows.Count).End(xlUp).Row - 1)

tblRng.Name = "Ranges"

With ActiveSheet.PivotTables("PPMain").PivotFields("SubBill No")
        .Orientation = xlRowField
        .Position = 1
End With
With ActiveSheet.PivotTables("PPMain").PivotFields("SubBill No")
        .Orientation = xlPageField
        .Position = 1
End With
    
With ActiveSheet.PivotTables("PPMain")
    If pivotItemExists("R50", .PivotFields("SubBill No")) Then
        .PivotFields("SubBill No").PivotItems("R50").Visible = False
    End If
End With

For Each pt In ActiveSheet.PivotTables
    pt.RefreshTable
Next pt

With Worksheets("Main Page").PivotTables("PPMain")
    If pivotItemExists("R50", .PivotFields("SubBill No")) Then
        .PivotFields("SubBill No").PivotItems("R50").Visible = False
    End If
    If pivotItemExists("R01", .PivotFields("SubBill No")) Then
        .PivotFields("SubBill No").PivotItems("R01").Visible = False
    End If
End With
'Worksheets("Main Page").PivotTables("PPMain").PivotFields("SubBill No").PivotItems("R50").Visible = False
'Worksheets("Main Page").PivotTables("PPMain").PivotFields("SubBill No").PivotItems("R01").Visible = False
'Worksheets("Main Page").PivotTables("PPMain").PivotFields("Destination").PivotItems("(blank)").Visible = False

For Each pt In ActiveSheet.PivotTables
    pt.RefreshTable
Next pt

rep.Range("A9:L1000").ClearContents
rep.Range("M9:M1000").Clear
rep.Range("N9:R1000").ClearContents
rep.Range("K9:K1000").Value = " "
irow = mp.Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

For i = 5 To irow
rep.Cells(i + 4, 1).Value = mp.Cells(i, 2).Value ' shipper name
rep.Cells(i + 4, 2).Value = mp.Cells(i, 3).Value
rep.Cells(i + 4, 3).Value = mp.Cells(i, 4).Value
rep.Cells(i + 4, 4).Value = mp.Cells(i, 1).Value ' probill num
rep.Cells(i + 4, 5).Value = mp.Cells(i, 5).Value
rep.Cells(i + 4, 6).Value = mp.Cells(i, 6).Value
rep.Cells(i + 4, 7).Value = mp.Cells(i, 7).Value
rep.Cells(i + 4, 8).Value = mp.Cells(i, 8).Value
rep.Cells(i + 4, 9).Value = mp.Cells(i, 9).Value
rep.Cells(i + 4, 10).Value = mp.Cells(i, 10).Value
rep.Cells(i + 4, 11).Value = mp.Cells(i, 11).Value
rep.Cells(i + 4, 12).Value = mp.Cells(i, 12).Value
rep.Cells(i + 4, 13).Value = mp.Cells(i, 13).Value
rep.Cells(i + 4, 14).Value = mp.Cells(i, 14).Value
rep.Cells(i + 4, 15).Value = mp.Cells(i, 15).Value
rep.Cells(i + 4, 16).Value = mp.Cells(i, 16).Value
rep.Cells(i + 4, 17).Value = mp.Cells(i, 17).Value
rep.Cells(i + 4, 18).Value = mp.Cells(i, 18).Value

Next

irow = rep.Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

'colour status column for friday pickup days

For i = 9 To irow
If Weekday(rep.Cells(i, 6)) = 6 And rep.Cells(i, 6) <> rep.Cells(i, 7) Then
rep.Cells(i, 13).Interior.Color = RGB(225, 194, 105)
End If

'Colour in Monday delivery dates
    If rep.Cells(i, 11) = "" Then GoTo line1 Else GoTo line2
    
line1: i = i + 1

line2: If Weekday(rep.Cells(i, 11)) = 2 And Weekday(rep.Cells(i, 10)) = 6 And rep.Cells(i, 20) = "" _
Or Weekday(rep.Cells(i, 11)) = 2 And Weekday(rep.Cells(i, 10)) = 7 And rep.Cells(i, 20) = "" _
Or Weekday(rep.Cells(i, 11)) = 2 And Weekday(rep.Cells(i, 10)) = 1 And rep.Cells(i, 20) = "" Then

rep.Cells(i, 13).Interior.Color = RGB(237, 237, 4)
rep.Cells(i, 13).Value = "Y"

End If
Next



Worksheets("On Time Report").Activate

MsgBox "Schneider On Time Report Generated", vbInformation, "Report Generated!"

End Sub

Function pivotItemExists(pfSearch As String, pf As PivotField) As Boolean
Dim pi As PivotItem

    For Each pi In pf.PivotItems
        If pi.Name = pfSearch Then
            pivotItemExists = True
            Exit Function
        End If
    Next pi
    pivotItemExists = False
    
End Function

Select allOpen in new window

See attached.

cheers,

Dave
sample-r1.xls

Idle curiosity - if you have just inserted a new blank column to ensure that the field name exists in your table, how do you expect it to have any items at all in the pivot table?

Ow the lady who inserts the raw data said that some days she wouldn't have the (above) mentioned column and somedays it would have it.

I will check this code asap and reply back!

Thanks!

Thanks all! 'On error resume next' did the trick