Example of Building a PivotTable with Excel VBA

We present a simple example of creating a PivotTable for a data list with the following fields: Client, Country, Date, Amount. Place a button on the worksheet that calls the CreateStylePivotTable() procedure when clicked.

Put the code  into a standard module. Check what PivotTable you get on the new sheet).

Creating a formatted PivotTable (Standard Module)

Sub CreateStylePivotTable()
    Dim DCache As PivotCache
    Dim Sales As PivotTable
    ' Create the cache
    Set DCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, SourceData:=Range("A1").CurrentRegion)
    ' Add a new worksheet to the workbook
    Worksheets.Add
    ' Create the PivotTable
    Set Sales = ActiveSheet.PivotTables.Add( _
        PivotCache:=DCache, TableDestination:=Range("A3"))
    ' Define the PivotTable layout
    With Sales
        .PivotFields("Страна").Orientation = xlPageField      ' Country → Page (Filter)
        .PivotFields("Дата").Orientation = xlColumnField      ' Date → Columns
        .PivotFields("Клиент").Orientation = xlRowField       ' Client → Rows
        .PivotFields("Стоимость").Orientation = xlDataField    ' Amount → Values
        ' Hide field captions
        .DisplayFieldCaptions = False
        ' Apply a PivotTable style
        .TableStyle2 = "PivotStyleDark5"
    End With
End Sub