在生成数据透视图时应用"PivotField.Autosort“

Question

我正在创建一个宏来生成一些聚集的列透视图。我使用了互联网/堆栈溢出中的示例来生成数据透视表，生成数据透视表图表，并按降序显示列。

我查阅了微软的文档，并尝试修改PivotField.Autosort的语法。

这是我的代码的一个带有注释的通用版本。按降序显示列的自动排序命令是最后两行：

'Declare Variables
Dim PSheet As Worksheet 'To create a sheet for a new pivot table.
Dim DSheet As Worksheet 'To use as a data sheet.
Dim PCache As PivotCache 'To use as a name for pivot table cache.
Dim PTable As PivotTable 'To use as a name for our pivot table.
Dim PRange As Range 'to define source data range.
Dim LastRow As Long 'To get the last row and column of our data range.
Dim LastCol As Long '

Dim chtObj      As ChartObject
Dim PvtSht      As Worksheet
Dim PvtTbl      As PivotTable

'After inserting a new worksheet, this code will set the value of FSheet
'variable to pivot table worksheet and DSheet to source data worksheet.
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("MaintActivType").Delete
Worksheets("Sheet1").Select
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "MaintActivType"
Application.DisplayAlerts = True
Set PSheet = Worksheets("MaintActivType")
Set DSheet = Worksheets("Sheet1")
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)

'Define Pivot Cache
'In Excel 2000 and above, before creating a pivot
'table you need to create a pivot cache to define the data source.
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="MaintenanceData")

'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="MaintenanceData")

'Insert Row Fields
With ActiveSheet.PivotTables("MaintenanceData").PivotFields("MaintActivType")
    .Orientation = xlRowField
    .Position = 1 'this field allows for sub-categories of data to be displayed
End With

'Insert Column Fields
With ActiveSheet.PivotTables("MaintenanceData").PivotFields("MaintActivType")
    .Orientation = xlDataField
    .Position = 1
    .Function = xlCount
    .Name = "Count of MaintActivType"
End With

' set the Pivot sheet
Set PvtSht = Sheets("MaintActivType")

' set the Pivot Table object
Set PvtTbl = PvtSht.PivotTables("MaintenanceData")

' set the Chart Object
Set chtObj = PvtSht.ChartObjects.Add(300, 200, 550, 200)

' modify ChartObject properties
With chtObj
    .Chart.SetSourceData PvtTbl.TableRange2 ' set the chart's data range to the Pivot-Table's TableRange2
    .Chart.ChartType = xlColumnClustered
    .Name = "Maintenance Activity Type"
    .Chart.HasTitle = True
    .Chart.ChartTitle.Text = "Maintenance Activity Type"
    .Chart.HasLegend = False
End With

ActiveSheet.PivotTables("MaintenanceData").PivotField("MaintActivType") _
  .AutoSort xlDescending, "Sum of MaintActivType"
'End With

我没有得到任何与自动排序命令相关的错误消息，但是如果我注释掉On Error Resume Next，我会得到一个透视缓存创建的类型不匹配错误(“设置Pcache...”)，这是令人费解的，因为这部分在错误消息被抑制的情况下工作得很好。

Answer 1

“该部分工作得很好，但错误消息被抑制了”，它不能正常工作，它不工作也没关系。

Dim PCache As PivotCache

'...
'...

Set PCache = ActiveWorkbook.PivotCaches.Create _
          (SourceType:=xlDatabase, SourceData:=PRange). _
          CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
          TableName:="MaintenanceData")

在这里，您创建了一个数据透视表，然后使用它创建了一个数据透视表，并尝试将该数据透视表赋给您的PCache变量(该变量只能用于数据透视表...)因此，这会引发运行时错误，但此时已创建了数据透视表，您可以在下一行继续执行该操作。

VBA Copy Pivot Table to New Sheet