+- QP School (https://qomplainerzschool.lima-city.de)
+-- Forum: Tutorials (https://qomplainerzschool.lima-city.de/forumdisplay.php?fid=3)
+--- Forum: Excel VBA Tutorials (https://qomplainerzschool.lima-city.de/forumdisplay.php?fid=48)
+--- Thread: Create a pivot table based on data in a worksheet with VBA (/showthread.php?tid=5258)
Create a pivot table based on data in a worksheet with VBA - Qomplainerz - 07-27-2023
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.