Things to edit:
Set DSheet = Worksheets("Sheet1") '''Source Data Sheet Name''' With ActiveSheet.PivotTables("PivotTable").PivotFields("#######") .Orientation = xlDataField .Function = xlMax .Position = 2
https://excelchamps.com/blog/vba-to-create-pivot-table/
Sub CreatePivotTable() Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long On Error Resume Next Application.DisplayAlerts = False Worksheets("PivotTable").Delete Sheets.Add Before:=PSheet PSheet.Name = "PivotTable" Application.DisplayAlerts = True Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets("Sheet1") '''Source Data Sheet Name''' 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) Set PCache = ThisWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange). _ CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ TableName:="PivotTable") Set PTable = PCache.CreatePivotTable _ (TableDestination:=PSheet.Cells(1, 1), TableName:="PivotTable") 'https://docs.microsoft.com/en-us/office/vba/api/excel.pivotfield.orientation With PSheet.PivotTables("PivotTable").PivotFields("Customers") .Orientation = xlRowField .Position = 1 End With With PSheet.PivotTables("PivotTable").PivotFields("Orders") .Orientation = xlDataField .Position = 1 End With With PSheet.PivotTables("PivotTable").PivotFields("Total") .Orientation = xlDataField .Function = xlMax .Position = 2 End With End Sub