Sub AddPovitTable()
'Constance
Const DATA_SHEET As String = "Advanced Filter"
Const DATA_ADDRESS As String = "R7C1:R107C11"
Const PIVOT_SHEET As String = "PivotSheet" ' Sheet Name with No space
Const PIVOT_ADDRESS As String = "R3C1"
Const PIVOT_TABLE As String = "Pivot Table"
'Object
Dim Wb As Workbook
Dim Sht As Worksheet
Dim pvtTable As PivotTable
Set Wb = ThisWorkbook
'Do not display Alert when delete a sheet
Application.DisplayAlerts = False
For Each Sht In Wb.Worksheets
If Sht.Name = PIVOT_SHEET Then Sht.Delete
Next Sht
Application.DisplayAlerts = True
'Add a new sheet for pivot table
Set Sht = Wb.Worksheets.Add(After:=Wb.Worksheets(Wb.Worksheets.Count))
Sht.Name = PIVOT_SHEET
'Create a new pivot table
Set pvtTable = Wb.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=DATA_SHEET & "!" & DATA_ADDRESS, _
Version:=xlPivotTableVersion15). _
CreatePivotTable( _
TableDestination:=PIVOT_SHEET & "!" & PIVOT_ADDRESS, _
TableName:=PIVOT_TABLE, _
DefaultVersion:=xlPivotTableVersion15)
'set Fields
With pvtTable
.PivotFields("State").Orientation = xlRowField
.PivotFields("State").Position = 1
.PivotFields("City").Orientation = xlRowField
.PivotFields("City").Position = 2
.PivotFields("Salesperson").Orientation = xlRowField
.PivotFields("Salesperson").Position = 3
.PivotFields("Payment").Orientation = xlRowField
.PivotFields("Payment").Position = 4
.PivotFields("Transport").Orientation = xlRowField
.PivotFields("Transport").Position = 5
.PivotFields("Month").Orientation = xlRowField
.PivotFields("Month").Position = 6
.AddDataField .PivotFields("Product A"), "Sum:Product A", xlSum
.AddDataField .PivotFields("Product B"), "Sum:Product B", xlSum
.AddDataField .PivotFields("Product C"), "Sum:Product C", xlSum
End With
'Release Objects
Set Wb = Nothing
Set Sht = Nothing
Set pvtTable = Nothing
End Sub
