与数据在同一工作表上的数据透视表

I have created various Pivot Tables but only in other worksheets (inserting new ones) from data. Now, I have the data in the same excel sheet I want to create the Pivot Table. It keeps crushing saying it has problem in the line where I set the PCache. I am providing the code below

我已经创建了各种数据透视表,但仅限于其他工作表(插入新工作表)。现在,我有相同的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

2 个解决方案

#1

Please Replace these lines :

请替换这些行:

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")

With:

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

And Rename TableName to PivotTable1

并将TableName重命名为PivotTable1

#2

Read the code below and explanation inside the code's comments:

阅读以下代码并在代码注释中解释:

Option Explicit

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

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

' first: set the Pivot Cache object
Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange.Address(False, False, xlA1, xlExternal))

' Second: set the Pivot Table object
Set PTable = PSheet.PivotTables.Add(PivotCache:=PCache, TableDestination:=PSheet.Cells(2, 13), TableName:="PivotTable1")

' after we set the Pivot-Table object, use the object to modify it's properties
With PTable
    With .PivotFields("Destination")
        .Orientation = xlRowField
        .Position = 1
        .Subtotals(1) = True
        .Subtotals(1) = False
    End With

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

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

End Sub

#1