Create a pivot table based on data in a worksheet with VBA

[Qomplainerz]

Example:

Sub CreatePivotTable()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim ptCache As PivotCache
    Dim ptRange As Range

    'Set the worksheet where the data is located
    Set ws = ThisWorkbook.Sheets("Sheet1")

    'Set the range of data for the pivot table (adjust the range as per your data)
    Set ptRange = ws.Range("A1:B10")

    'Create a new pivot cache based on the data range
    Set ptCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ptRange)

    'Create a new pivot table on a new worksheet
    Set pt = ptCache.CreatePivotTable(TableDestination:=ws.Cells(1, 4), TableName:="MyPivotTable")
    With pt
        .PivotFields("Category").Orientation = xlRowField
        .AddDataField .PivotFields("Value"), "Sum of Value", xlSum
    End With
End Sub

Explanation:

The code defines a subroutine named CreatePivotTable.
It declares variables for the worksheet (ws), pivot table (pt), pivot cache (ptCache), and the range of data for the pivot table (ptRange).
Set ws = ThisWorkbook.Sheets("Sheet1") specifies the worksheet ("Sheet1") where the data is located.
The ptRange variable is set to the range of data for the pivot table (A1:B10 in this example).
A new pivot cache is created based on the data range using ThisWorkbook.PivotCaches.Create.
The pivot table is created on a new worksheet at cell D1 (TableDestination:=ws.Cells(1, 4)) with the name "MyPivotTable" using ptCache.CreatePivotTable.
The With block is used to customize the pivot table. 
In this example, the "Category" field is set as a row field, and the "Value" field is added as a data field with the aggregation function xlSum.