CODEWITHSUNDEEP
vbaexcel
AmadeusNing
AmadeusNing

Reputation: 107

Excel VBA pivot item error 424

I am working on the following code to generate a pivot table. For Each pivot_item In PvtTb1.PivotFields("Period").PivotItems here got a 424 error, which is "object required".

However I'm not sure what object I'm missing. The code was working well without the the section that was flagged.

Can I fix this? 

Dim PvtTbl As PivotTable
Dim wsData As Worksheet
Dim rngData As Range
Dim PvtTblCache As PivotCache
Dim wsPvtTbl As Worksheet
Dim pvtFld As PivotField

Set wsData = Worksheets("Verify")
Set wsPvtTbl = Worksheets("Summary")

wsPvtTbl.Cells.Clear
wsPvtTbl.Cells.ColumnWidth = 10

For Each PvtTbl In wsPvtTbl.PivotTables
    If MsgBox("Delete existing PivotTable!", vbYesNo) = vbYes Then
        PvtTbl.TableRange2.Clear
    End If
Next PvtTbl

Set PvtTblCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:="Verify")
    PvtTblCache.CreatePivotTable TableDestination:=wsPvtTbl.Range("B19"), _
        TableName:="PivotTable1"

Set PvtTbl = wsPvtTbl.PivotTables("PivotTable1")

PvtTbl.ManualUpdate = True

'add row, column and page (report filter) fields:
Set pvtFld = PvtTbl.PivotFields("Period")
pvtFld.Orientation = xlPageField

With wsPvtTbl.PivotTables("PivotTable1").PivotFields("Period")
    On Error Resume Next
    .PivotItems("N").Visible = False
    .PivotItems("Y").Visible = True
    On Error GoTo 0
End With

Dim pivot_item As PivotItem

Set pvtFld = PvtTbl.PivotFields("Group")
pvtFld.Orientation = xlRowField

Set pvtFld = PvtTbl.PivotFields("Name")
pvtFld.Orientation = xlRowField
pvtFld.Position = 2

For Each pivot_item In PvtTb1.PivotFields("Period").PivotItems
    If pivot_item.Name = "Y" Then
        Set pvtFld = PvtTb1.PivotFields("PROCESS_DATE")
            pvtFld.Orientation = xlColumnField
        With PvtTbl.PivotFields("NO_REC")
            .Orientation = xlDataField
            .Function = xlSum
            .NumberFormat = "#,##0"
            .Position = 1
        End With
    End If
Next pivot_item

Upvotes: 0

Views: 456

Answers (2)

Shai Rado
Shai Rado

Reputation: 33692

Since you already Set the PvtTbl object with this line Set PvtTbl = wsPvtTbl.PivotTables("PivotTable1"), you can simplify your life, and avoide future errors like you have here (mixing PvtTbl and PvtTb1), by using the With statemnet, like in the shorter version (and "cleaner" ) code below.

' modify the Pivot-Table properties, afeter you set it   
With PvtTbl
    .ManualUpdate = True

    'add row, column and page (report filter) fields:

    With .PivotFields("Period")
        .Orientation = xlPageField
        On Error Resume Next
        .PivotItems("N").Visible = False
        .PivotItems("Y").Visible = True
        On Error GoTo 0
    End With

    Dim pivot_item As PivotItem

    .PivotFields("Group").Orientation = xlRowField

    With .PivotFields("Name")
        .Orientation = xlRowField
        .Position = 2
    End With

    For Each pivot_item In .PivotFields("Period").PivotItems
        If pivot_item.Name = "Y" Then
            .PivotFields("PROCESS_DATE").Orientation = xlColumnField
            With .PivotFields("NO_REC")
                .Orientation = xlDataField
                .Function = xlSum
                .NumberFormat = "#,##0"
                .Position = 1
            End With
        End If
    Next pivot_item
End With

Upvotes: 0

Egan Wolf
Egan Wolf

Reputation: 3573

In PvtTb1 you have "1" (one, a digit) at the end, should be "l" (a letter).

Upvotes: 2

Related Questions