Error while creating pivot table: "Application Defined or object Defined Error."

Question

I am creating a pivot table. I have three sheets and I am creating the pivot table in sheet CAT_Pivot with data in a preparation sheet.

I am able to achieve my target, but I get a runtime error at the end of the execution. The runtime error states

Application Defined or object Defined Error.

To add to that, how do I calculate the values in the pivot table? I used .function = xlcount and I didn't succeed.

Here is my code:

Sub AutoPivot()

Dim PvtCache            As PivotCache
Dim PvtTbl              As PivotTable
Dim PvtSht              As Worksheet

' set Pivot Cache for Pivot Table
' Your range is static, there are ways to refer to a dynamic range
Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Preparation sheet!R1C1:R1048576C8")

' set the Pivot table's sheet
Set PvtSht = Worksheets("CAT_Pivot")

' add this line in case the Pivot table doesn't exit >> first time running this Macro
On Error Resume Next
Set PvtTbl = PvtSht.PivotTables("PivotTable1") ' check if "PivotTable7" Pivot Table already created (in past runs of this Macro)

On Error GoTo 0
If PvtTbl Is Nothing Then ' Pivot table object is nothing >> create it

    ' create a new Pivot Table in "PivotTable4" sheet
    Set PvtTbl = PvtSht.PivotTables.Add(PivotCache:=PvtCache, TableDestination:=PvtSht.Range("A3"), TableName:="PivotTable1")

    With PvtTbl
        With .PivotFields("Category")
            .Orientation = xlRowField
            .Position = 1
        End With
        With .PivotFields("Colour")
            .Orientation = xlColumnField
            .Position = 1
        End With
        With .PivotFields("Category")
            .PivotItems("DG-035583").Visible = False
            .PivotItems("DG-048917").Visible = False
            .PivotItems("DG-Series").Visible = False
            .PivotItems("gn").Visible = False
            .PivotItems("yl").Visible = False
            .PivotItems("(blank)").Visible = False
        End With
        With .PivotFields("Colour")
            .PivotItems("(blank)").Visible = False
        End With

    End With
Else
    ' just refresh the Pivot cache with the updated Range
    PvtTbl.ChangePivotCache PvtCache
    PvtTbl.RefreshTable
End If

End Sub

Vityata · Answer 1 · 2017-06-20T10:22:42.380

0

The problem is that you are trying to write nested with statements. Try to rewrite your code from scratch without using more than one with at a time.

E.g., this part is bad:

With PvtTbl
    With .PivotFields("Category")
        .Orientation = xlRowField
        .Position = 1
    End With
    With .PivotFields("Colour")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With .PivotFields("Category")
        .PivotItems("DG-035583").Visible = False
        .PivotItems("DG-048917").Visible = False
        .PivotItems("DG-Series").Visible = False
        .PivotItems("gn").Visible = False
        .PivotItems("yl").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    With .PivotFields("Colour")
        .PivotItems("(blank)").Visible = False
    End With

End With

You may rewrite it like this:

With PvtTbl.PivotFields("Category")
    .Orientation = xlRowField
    .Position = 1
End With
With PvtTbl.PivotFields("Colour")
    .Orientation = xlColumnField
    .Position = 1
End With

Reference for nested if - Nested With Statement hiearchy

Cheers!

Edit: Actually nested if is not that bad, it even works. But still, its a bit confusing.

edited Jun 20 '17 at 10:22

answered Jun 20 '17 at 09:23

Vityata

42,633
8
55
100

Thank you. I will rewrite it and try. – Mikz Jun 20 '17 at 09:36
@Mikz - also, try to build a smaller model of your problem and post it if you still experience it. E.g., just a few `PivotItems` and `PivotFields`. – Vityata Jun 20 '17 at 09:38
I tried the way you suggested, the error still exists. – Mikz Jun 20 '17 at 09:41
@Mikz - on which line do you get it exactly? Add the info to the question. – Vityata Jun 20 '17 at 09:41
means you Need an snapshot of my Pivot table ? – Mikz Jun 20 '17 at 09:43
@Mikz - means that you get a yellow line in the VB Editor when you get the error. Where is this line? – Vityata Jun 20 '17 at 09:43
@Vityanta I tried it in each line. It doesnt Point the error any where in the code. But when i run it as whole, then i get that error – Mikz Jun 20 '17 at 09:47
@Mikz - have you tried this - https://stackoverflow.com/questions/23883640/pivots-and-run-time-error-1004-application-defined-or-object-defined-error – Vityata Jun 20 '17 at 09:50
I will go through that and get back to you. – Mikz Jun 20 '17 at 09:52
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/147158/discussion-between-mikz-and-vityata). – Mikz Jun 20 '17 at 11:28

Error while creating pivot table: "Application Defined or object Defined Error."

1 Answers1