PivotTable_VBA

Sub test()

'Step 4: Create Pivot table

'Declare Variables
Dim PSheet As Worksheet 'used sht before
Dim DSheet As Worksheet 'used sht before
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range 'used rng before
Dim LastRow As Long 'used this before
Dim LastCol As Long

'Insert a new blank worksheet
On Error Resume Next
Application.DisplayAlerts = False 'https://bettersolutions.com/vba/macros/application-displayalerts.htm
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("Data")

'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="SalesPivotTable")

'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

'Insert Row Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("group")
.Orientation = xlRowField
.Position = 1
End With

'Insert Column Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Date")
.Orientation = xlColumnField
.Position = 1
End With

'Insert Data field (Count of "group")
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("group")
.Orientation = xlDataField
.Position = 1
.Function = xlCount
End With

'Sort "Grand Total" by Largest to Smallest


'ActiveSheet.PivotTables("SalesPivotTable").PivotFields("group") _
'.AutoSort xlDescending, "Grand Total"

'With ActiveSheet.PivotTables("SalesPivotTable").PivotField("group")
'.Orientation = xlColumnField
'.AutoSort xlDescending
'End With
'
'With Activesheet.PivotTables("SalesPivotTable").PivotFields("group").AutoSort _
'        xlAscending
'End With


End Sub