Wednesday 17 December 2014

Microsfot Excel - Macro - How to create Pivot Table


Watch this example on YouTube

Here is my data in columns A, B, C















 The following macro
Sub test()
    Dim ws As Worksheet
    Dim pc As PivotCache
    Dim pt As PivotTable
   
    Set ws = Worksheets.Add
    Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, "Sheet1!R1C1:R7C3")
    Set pt = pc.CreatePivotTable(ws.Range("A2"))
   
    With pt
        With .PivotFields("Name")
            .Orientation = xlRowField
            .Position = 1
        End With
       
        .AddDataField .PivotFields("Salary"), "Salary Total", xlSum
    End With
End Sub

will create this pivot table:











No comments:

Post a Comment