CODEWITHSUNDEEP
vbaexcelpivot-table
Jay
Jay

Reputation: 646

Excel VBA Creating Pivot not fully working

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

Upvotes: 0

Views: 1660

Answers (1)

Rory
Rory

Reputation: 34045

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

Upvotes: 1

Related Questions