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:=ActiveSheet
ActiveSheet.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 = ActiveWorkbook.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 ActiveSheet.PivotTables("PivotTable").PivotFields("Customers")
.Orientation = xlRowField
.Position = 1
End With

With ActiveSheet.PivotTables("PivotTable").PivotFields("Orders")
.Orientation = xlDataField
.Position = 1
End With

With ActiveSheet.PivotTables("PivotTable").PivotFields("Total")
.Orientation = xlDataField
.Function = xlMax
.Position = 2
End With

End Sub
  • No labels