Pivot table code example with excel

How to create a pivot table using VBA code in Excel:

Sub CreatePivotTable()

    ' Define the data range for the pivot table
    Dim DataRange As Range
    Set DataRange = ActiveSheet.Range("A1:D100")
    
    ' Define the location for the pivot table
    Dim PivotLocation As Range
    Set PivotLocation = ActiveSheet.Range("F1")
    
    ' Create the pivot cache
    Dim PivotCache As PivotCache
    Set PivotCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=DataRange)
    
    ' Create the pivot table
    Dim PivotTable As PivotTable
    Set PivotTable = PivotLocation.PivotTableWizard( _
        SourceType:=xlDatabase, _
        SourceData:=PivotCache, _
        TableDestination:=PivotLocation, _
        TableName:="Sales Pivot Table")
    
    ' Add fields to the pivot table
    With PivotTable
        .AddDataField .PivotFields("Sales"), "Total Sales", xlSum
        .AddDataField .PivotFields("Units"), "Total Units", xlSum
        .PivotFields("Sales Rep").Orientation = xlRowField
        .PivotFields("Product").Orientation = xlColumnField
    End With
    
End Sub

In this example, the code creates a pivot table that shows the total sales and total units for each sales representative and product. Here’s a breakdown of what the code is doing:

1. Define the data range for the pivot table (in this case, columns A to D on the active sheet).
2. Define the location for the pivot table (in this case, cell F1 on the active sheet).
3. Create a pivot cache using the data range.
4. Create a pivot table using the pivot cache and the specified location.
5. Add the “Sales” and “Units” fields to the pivot table as data fields, and set the calculation type to “Sum”.
6. Add the “Sales Rep” and “Product” fields to the pivot table as row and column fields, respectively.

This code is just an example, and you can modify it to suit your specific needs.

CHECK MORE at https://itexamtools.com/