VBA Code:
Sub CreateDynamicPivotTable()
Dim sourceData As Range
Dim pivotTable As PivotTable
Dim pivotCache As PivotCache
Dim pivotWorksheet As Worksheet
' Set the source data range for the pivot table
Set sourceData = Worksheets("Sheet1").Range("A1:C100") ' Change the sheet name and range as desired
' Create a new pivot cache
Set pivotCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=sourceData)
' Create a new worksheet for the pivot table
Set pivotWorksheet = ThisWorkbook.Worksheets.Add
' Create a new pivot table on the pivot worksheet
Set pivotTable = pivotWorksheet.PivotTables.Add(PivotCache:=pivotCache, TableDestination:=pivotWorksheet.Range("A1"))
' Specify the pivot table fields and settings
With pivotTable
.RowFields.Add DataField:=pivotTable.PivotFields("Category")
.ColumnFields.Add DataField:=pivotTable.PivotFields("Month")
.AddDataField pivotTable.PivotFields("Amount"), "Sum of Amount", xlSum
End With
' Format the pivot table as desired
' Autofit columns
pivotWorksheet.UsedRange.Columns.AutoFit
' Apply a pivot table style
pivotTable.TableStyle2 = "PivotStyleMedium9"
' Inform the user that the pivot table has been created
MsgBox "The pivot table has been created on a new worksheet."
End Sub
Join Our Telegram Group
Join Our WhatsApp Group
