vba - 数据透视表位于同一工作表上

Question

我已经根据数据创建了各种数据透视表，但仅在其他工作表(插入新工作表)中。现在，我在同一个 Excel 工作表中拥有数据，我想创建数据透视表。它一直说我设置 PCache 的行有问题。我提供下面的代码

Sub a()

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

Set PSheet = ActiveSheet

LastRow = PSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = PSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = PSheet.Cells(1, 1).Resize(LastRow, LastCol)

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange).CreatePivotTable TableDestination:=PSheet.Cells(2, 13), TableName:="PivotTable1"

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Destination")
    .Orientation = xlRowField
    .Position = 1
    .Subtotals(1) = True
    .Subtotals(1) = False
End With

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Total trucks")
    .Orientation = xlDataField
    .Position = 1
    .Function = xlSum
    .Name = "Sum of Quantity (cases/sw)"
End With

'PTable.LayoutRowDefault = xlTabularRow
'Range("M2").Value = "Commodity Code"

End Sub

Answer 1

请替换这些行:

Set PCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange).CreatePivotTable(TableDestination:=PSheet.Cells(2, 13), TableName:="PivotTable")

Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="PivotTable")

与:

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

并将TableName重命名为PivotTable1