CODEWITHSUNDEEP
vbaexcelpivot-table
Simon
Simon

Reputation: 19938

Pivots and Run-time error 1004: Application-defined or object-defined error

I'm quite new to macros and VBA coding and I'm trying to create a really simple macro that takes the data from a table with 33 columns and convert it to a pivot.

Only the last 3 columns (31, 32, 33) contains numbers and I need that to appear on the pivot as I want to compare current month, last month and the movement from month on month.

Here is my code so far:

Sub Macro6()
'
' Macro6 Macro
'

'
    Dim shtSrc As Worksheet, shtDest As Worksheet
    Dim pc As PivotCache

    Set shtSrc = ActiveSheet

    Set shtDest = shtSrc.Parent.Sheets.Add()
    shtDest.Name = shtSrc.Name & "-Pivot"

    Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=shtSrc.Range("A1").CurrentRegion)
    pc.CreatePivotTable TableDestination:=shtDest.Range("A3"), _
        TableName:="PivotTable1"

    With shtDest.PivotTables("PivotTable1")
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With

    Set shtDest = ActiveSheet

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Name")
        .Orientation = xlRowField
        .Position = 1
    End With

    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields(31), "Last month", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields(32), "This month", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields(33), "Movement", xlSum



End Sub

I get an error Run-time error 1004: Application-defined or object-defined error on this line:

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields(33), "Movement", xlSum

If I take away this line, the macro works fine but only creates 2 column. If I put in the line, it generates the error and does not create the 3rd column.

Can anyone explain why?

Upvotes: 3

Views: 9597

Answers (2)

Jim
Jim

Reputation: 1

Another explanation is that the Row Label may have changed from Date Filter to a Label filter after a refresh. In particular I pull data in from a SQL query I need to convert my date range data type to date format by using text to columns after my refresh, then I will refresh my pivot table.

Check if your drop down for Row Label shows date filter instead of label filter to verify you have the correct format from your data source.

Upvotes: -1

Simon
Simon

Reputation: 19938

OK. I think I figured it out, but it is quite strange that this is happening, is it a bug?

The reason is that in my source table, the column heading says "Movement". If I change the caption to something other than the Column heading for example, by saying "MovementS", then the macro works.

 ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields(33), "Movements", xlSum

Upvotes: 3

Related Questions