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

Question

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?

score 2 · Accepted Answer · answered May 27 '14 at 07:59

2

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

answered May 27 '14 at 07:59

Simon

19,658
27
149
217

Hmm...that's odd. I'll try to reproduce the error when I get a minute. In the meantime you should look at [How to avoid Activate/Select](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select). It's often the culprit of 1004 errors. – RubberDuck May 29 '14 at 10:58
Cool. Please try it on your side and let me know. – Simon May 29 '14 at 16:50

score -1 · Answer 2 · edited Apr 02 '15 at 15:16

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.

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

2 Answers2

Linked