对数据透视表VBA进行排序

OBJECTIVE

按降序排序数据透视表值 .

APPROACH

• 删除上一个数据透视表（ PIVOT ）

• 设置新的数据透视表位置（ target ）

• 创建PivotCache（ pvtCache ）

• Deply PivotTable（ pvt ）

• 添加数据透视表字段（ pvt.PivotFields(_) ）

• ISSUE: Sort PivotTable field (PivotField("Base Expense")) in descending order

CODE

Sub createPivot()

Dim ws As Worksheet
Dim pvtCache As pivotCache
Dim pvt As pivotTable
Dim srcData As String
Dim lastRow As Long
Dim startPvt As String
Dim target As Worksheet

'Delete previous pivottable
Worksheets("PIVOT").PivotTables("PivotTable1").TableRange2.Clear


'Select pivot table data
Worksheets("CONSOLIDATED").Activate
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
srcData = ActiveSheet.Name & "!" & Range("A1:H" & lastRow).Address(ReferenceStyle:=xlR1C1)

'Set pivot table location
Set target = ThisWorkbook.Worksheets("PIVOT")
startPvt = target.Name & "!" & target.Range("A1").Address(ReferenceStyle:=xlR1C1)

'Create pivot cache
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=srcData)

'Deploy pivot table
Set pvt = pvtCache.CreatePivotTable( _
    TableDestination:=startPvt, _
    TableName:="PivotTable1")

    'Add Pivot Fields
    pvt.PivotFields("Fiscal Year").Orientation = xlColumnField
    pvt.PivotFields("Fiscal Year").Position = 1

    pvt.PivotFields("Fiscal Month").Orientation = xlColumnField
    pvt.PivotFields("Fiscal Month").Position = 2

    pvt.PivotFields("Unit").Orientation = xlRowField
    pvt.PivotFields("Unit").Position = 1

    pvt.PivotFields("Project").Orientation = xlRowField
    pvt.PivotFields("Project").Position = 2

    pvt.PivotFields("Base Expense").Orientation = xlDataField

   'Sort by largest !!!ERROR!!!
    pvt.PivotField("Base Expense") _
        .AutoSort xlDescending, "Base Expense" 

End Sub

ERROR

“对象不支持此属性或方法”

@线

'Sort by largest !!!ERROR!!!
    pvt.PivotField("Base Expense") _
        .AutoSort xlDescending, "Base Expense"

QUESTIONS

• 不确定为什么抛出此错误 . 我搜索了文档，让我相信这应该工作（https://msdn.microsoft.com/en-us/library/office/ff834371.aspx）注意：ActiveSheet！=到工作表上的工作表，但我不认为这应该在这里创建一个问题

• 对任何有关代码重构的建议表示赞赏 .