Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata

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
  • No labels