Unable to get PivotItems property of the PivotField class [SOLVED]

[Asingham]

Hello,

I did a search for this concept before posting it here. I'm new to VB and don't know much about the programming concepts. However, by recording the macro, i'm able to modify a VB code for creating a pivot table in an excel 2003.

----------------------------------------------------------------------------------

Purpose: I have an excel sheet with around 4000 rows of data that usually gets update once in two weeks. It also has around 15 columns. I came up with a macro to create a pivot table for this data.

----------------------------------------------------------------------------------

What I did:

Sub CreatePivot()

' Creates a PivotTable report from the table on Sheet1

' by using the PivotTableWizard method with the PivotFields

' method to specify the fields in the PivotTable.

Dim objTable As PivotTable, objField As PivotField


' Select the sheet and first cell of the table that contains the data.

ActiveWorkbook.Sheets("Input Data").Select

Range("B1").Select


' Create the PivotTable object based on the Input data on Sheet1.

Set objTable = Sheet7.PivotTableWizard


' Specify row and column fields.

Set objField = objTable.PivotFields("Region")

objField.Orientation = xlRowField

objField.Position = 1

objField.PivotItems("AP").Visible = True

objField.PivotItems("NA").Visible = True

objField.PivotItems("EU").Visible = True

objField.PivotItems("None").Visible = False

objField.PivotItems("(blank)").Visible = False

objField.EnableMultiplePageItems = True


Set objField = objTable.PivotFields("Prod")

objField.Orientation = xlRowField

objField.Position = 2

objField.PivotItems("Aeropostale").Visible = True

objField.PivotItems("American Eagle").Visible = True

objField.PivotItems("Banana Republic").Visible = True

objField.PivotItems("Victoria Secret").Visible = True

objField.PivotItems("Hugo Boss").Visible = True

objField.PivotItems("Levis").Visible = True

objField.PivotItems("(blank)").Visible = False

objField.PivotItems("Victoria Secret").Position = 1

objField.PivotItems("American Eagle").Position = 2

objField.PivotItems("Hugo Boss").Position = 3

objField.EnableMultiplePageItems = True


' Specify a data field with its summary

' function and format.

Set objField = objTable.PivotFields("Proposal")

objField.Orientation = xlDataField

objField.Function = xlCount

objField.NumberFormat = " #,##0"

' Specify a page field.

Set objField = objTable.PivotFields("Prop Status")

objField.Orientation = xlPageField

objField.PivotItems("Quoted").Visible = True

objField.PivotItems("Quoted w/ Spec").Visible = True

objField.PivotItems("Quoted w/o Spec").Visible = True

objField.PivotItems("Quoted P Only").Visible = True

objField.PivotItems("Others").Visible = False

objField.PivotItems("Eng Review").Visible = False

objField.PivotItems("Feas Review").Visible = False

objField.PivotItems("No Bid").Visible = False

objField.PivotItems("On Hold").Visible = False

objField.PivotItems("Remove Eng Review").Visible = False

objField.PivotItems("(blank)").Visible = False

objField.PivotItems("Received").Visible = True

objField.EnableMultiplePageItems = True

' Preview the new PivotTable report.

ActiveSheet.PrintPreview


' Prompt the user whether to delete the PivotTable.

Application.DisplayAlerts = False

If MsgBox("Delete the PivotTable?", vbYesNo) = vbYes Then

ActiveSheet.Delete

End If

Application.DisplayAlerts = True


End Sub

--------------------------------------------------------------------------------

Issue:

It is working fine (i'm assuming - yet to do more tests) when all the pivot items in the pivot fields are part of the data. I deleted couple of rows in which the pivot item "(blank)" in the pivotfield "Purch. Reg" is not available. Usually such type of value can be part of the input data. I wonder this would be the same issue when any pivot field or other pivot item is missing in the input will result in the same error.

---------------------------------------------------------------------------------


Can some one please explain me what concepts should I learn to handle such errors. Thanks in advance.

 

[jeffreyweir]

You can use an ON ERROR RESUME NEXT statement to tell Excel to ignore any errors caused by pivotitems that don't exist:


ON ERROR RESUME NEXT

objField.PivotItems("Something").Visible = False

objField.PivotItems("SomethingElse").Visible = True

ON ERROR GOTO 0


Note that it is a bad idea to wrap large routines in a ON ERROR RESUME NEXT statement, because it hides any other errors that your code might generate, and you won't know it. That is, it doesn't fix errors, but just ignores them. THat said, I use the ON ERROR RESUME NEXT statement myself in this very situation when I can't guarantee whether pivotitems will exist in my data or not.


That ON ERROR GOTO 0 bit at the end tells Excel to NOT ignore errors again i.e. it tells Excel "If there's an error, tell me about it".

More info at http://www.cpearson.com/excel/errorhandling.htm

 

[jeffreyweir]

Note that if you want something more sophisticated, you can use something like this:

Put this at the top of your routine:

On Error GoTo errhandler


...and at the bottom, put this

[pre]errhandler:
If Err.Number <> 0 Then
Select Case Err.Description
Case "Unable to get the PivotItems property of the PivotField class": Resume Next
Case Else: MsgBox "Whoops, something went wrong: Error#" & Err.Number & vbCrLf & Err.Description _
, vbCritical, "Error", Err.HelpFile, Err.HelpContext
End Select
End If

[/pre]

This means that any errors that are specifically to do with the "Unable to get the PivotItems property of the PivotField class" warning will be ignored, but any other errors won't be ignored. THis stops the "On Error Resume Next" approach in my previous post from masking any other errors to do with other things.

 

[Asingham]

Thank You! This helped me resolve the error. Appreciate for your help