Pivot table creation has an unusual activity when done manually vs. VBA

Question

Okay, so I recorded a macro to create a pivot table. The problem is very simple and is very annoying. I am selecting(using vba) all my columns and rows with data in them on one sheet named "Data" and making pivot table on another sheet named "Pivot". But when I run the macro, it is not showing the last column("Days late") in the Field list, whereas when I select the same thing manually and insert a pivot table, it shows all the fields. What could I be doing wrong with such a simple step, I have no idea?

Sheets("Pivot").Select

Range("A:R").Delete

Sheets("Data").Select

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Data!R1C1:R1000C16", Version:=xlPivotTableVersion15).CreatePivotTable _
    TableDestination:="Pivot!R2C1", TableName:="PivotTable1", DefaultVersion _
    :=xlPivotTableVersion15

Sheets("Pivot").Select
Cells(1, 1).Select

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Vendor Name")

    .Orientation = xlRowField
    .Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Planner")
    .Orientation = xlRowField
    .Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Part Number")
    .Orientation = xlRowField
    .Position = 3
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Reason Code")
    .Orientation = xlRowField
    .Position = 4
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Comments")
    .Orientation = xlRowField
    .Position = 5
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Quantity Outstanding")
    .Orientation = xlRowField
    .Position = 6
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Need By Date")
    .Orientation = xlRowField
    .Position = 7
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Days Late")
    .Orientation = xlRowField
    .Position = 8
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Release Number")
    .Orientation = xlRowField
    .Position = 9
End With

Data Sheet

Pivot Fields List

1) add a screen shot of your Data worksheet. (Blank out actual values, leaving just column headers, if the information is sensitive). 2) What do you mean by it is not showing the last column("Days late") in the Field list? Maybe show a screen shot of that, too. — FreeMan

Trum Trum · Accepted Answer · 2015-04-29T12:38:13

This is an issue I have come across before and is caused by the order of the events. When the macro recorder does its thing - its very literal, and that's why it doesn't work when it 'plays it back'

I would look at re-ordering the events (the with statements) to see if this helps

Pivot table creation has an unusual activity when done manually vs. VBA

1 Answers