• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2573
  • Last Modified:

Unable to get the PivotItem property of the PivotField Class error

Worksheets("Main Page").PivotTables("PPMain").PivotFields("SubBill No").PivotItems("R50").Visible = False

Hi,

I am getting the 'Unable to get the PivotItem property of the PivotField Class error' on the above field in the attached sample file when running the code from vba.

Previously, I posted here and found out that there was no 'SubBill No' on the raw data...so I made the program to put in a SubBill No field/column and am refreshing so the pivot table captures this field too.

But the error is still on.

Any help is much appreciated!

Here is the code up to error point

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"

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

Worksheets("Main Page").PivotTables("PPMain").PivotFields("SubBill No").PivotItems("R50").Visible = False

Open in new window

Sample.xls
0
Shanan212
Asked:
Shanan212
  • 3
  • 3
  • 2
2 Solutions
 
Shanan212Author Commented:
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

Open 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

Open in new window

Sample.xls
0
 
dlmilleCommented:
You cannot check if a pivotitem exists using NOTHING.  That's like referencing an index to an array that doesn't have that dimension.

You have to trap this with error checking.  back to your original code:

on error resume next
Worksheets("Main Page").PivotTables("PPMain").PivotFields("SubBill No").PivotItems("R50").Visible = False
on error goto 0

If you need to check if a pivotitem exists, then

on error resume next
refer to the pivotitem
if err.number <> 0 then ' the item was not found


PS - tip to building a UDF for testing for existance: http://www.mrexcel.com/forum/showthread.php?t=317169

Cheers,

Dave
0
 
dlmilleCommented:
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
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
dlmilleCommented:
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

Open 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

Open in new window

See attached.

cheers,

Dave
sample-r1.xls
0
 
Rory ArchibaldCommented:
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?
0
 
Shanan212Author Commented:
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!
0
 
Rory ArchibaldCommented:
My point is just that you can't hide an item in a field that has no items anyway. Personally I would simply put On Error Resume Next in front of the line to hide the PI, and then resume normal error handling afterwards.
0
 
Shanan212Author Commented:
Thanks all! 'On error resume next' did the trick
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now