Excel VBA Creating Pivot not fully working

Question

I have a problem with Excel Pivot that I've been struggling to solve for hours now. I have the code below which I got from doing a Record Macro. It is supposed to create a Pivot table and add "Record Type" as first column then add another column for the total records for each record type but it deletes the 1st column. I am trying to do this http://youtu.be/UseY0lEPu80 but when I use VBA I get this problem http://youtu.be/eOxXX336gP0

S1LastRow = 569
S1LastColumn = 17
Sheet2.UsedRange.Delete
Application.Goto Sheet2.Range("A1"), True

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= "Main!R1C1:R" & S1LastRow & "C" & S1LastColumn, Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:="Graph!R1C1", TableName:="Summary", DefaultVersion :=xlPivotTableVersion12

With ActiveSheet.PivotTables("Summary").PivotFields("Record Type")
    .Orientation = xlRowField
    .Position = 1
End With

ActiveSheet.PivotTables("Summary").AddDataField ActiveSheet.PivotTables("Summary").PivotFields("Record Type"), "Count of Record Type", xlCount

With ActiveSheet.PivotTables("Summary")
    .ColumnGrand = False
    .RowGrand = False
End With

Add the field as a data field first, then as a row field and you won't have that problem. — Rory
– Rory, Commented Dec 2, 2014 at 16:50
Sorry Roy but could you explain more or maybe an example rearrangement of my code above? I thought I already did what you suggested though. The problem is the .AddDataField removes the previous column :s — Jay
– Jay, Commented Dec 2, 2014 at 18:29

Rory · Accepted Answer · 2014-12-03 08:14:39Z

1

Do it Like this:

Dim PC                    As Excel.PivotCache
Dim PT                    As Excel.PivotTable

S1LastRow = 569
S1LastColumn = 17
Sheet2.UsedRange.Delete
Application.Goto Sheet2.Range("A1"), True

Set PC = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                    SourceData:="Main!R1C1:R" & S1LastRow & "C" & S1LastColumn, _
                                    Version:=xlPivotTableVersion12)
Set PT = PC.CreatePivotTable(TableDestination:="Graph!R1C1", TableName:="Summary", _
                             DefaultVersion:=xlPivotTableVersion12)

With PT

    ' add the data field first if you want to use the same field as a row/column field too
    .AddDataField .PivotFields("Record Type"), "Count of Record Type", xlCount

    With .PivotFields("Record Type")
        .Orientation = xlRowField
        .Position = 1
    End With

    .ColumnGrand = False
    .RowGrand = False
End With

answered Dec 3, 2014 at 8:14

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jay Over a year ago

Ah I see, so the problem there was that I was using the same column as both Pivotfield and Datafield which rarely happens by the way. Is it safe to say that we should practice adding the Datafields first before the Pivotfield to avoid problems? I tried your code and it works but I went back to my code above and modified it so it does the .AddDataField first before the .PivotFields and it now works too.

Rory Over a year ago

Yes. When you add it as a Row/Column field the name doesn't change, so when you add it as a datafield, it moves the existing field. When you add it as a datafield, the name has to be different, so the subsequent code works to add a new copy of the field.

Collectives™ on Stack Overflow

Excel VBA Creating Pivot not fully working

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related