如何在没有类型不匹配错误的情况下使用定义的范围作为数据源创建数据透视缓存

Question

我试图创建一个相当简单的透视表，但在创建透视缓存时遇到了问题。数据源和数据类型似乎都是正确的，但我得到了一个错误。我搜索了类似的问题，但没有一个经过验证的答案对我有效

Sub Macro1()
'
' Macro1 Macro
'
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
'On Error Resume Next
'Application.DisplayAlerts = False
Worksheets("Regions").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "Regions"
Application.DisplayAlerts = True
Set PSheet = Worksheets("Regions")
Set DSheet = Worksheets("Sheet1")
'Define Data Range
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)
'Set Pivot table Cache
Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange).CreatePivotTable(TableDestination:=PSheet.Cells(2, 2),TableName:="SalesPivotTable")
Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")
'Insert Row Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("ID")
.Orientation = xlRowField
.Position = 1
End With
'Insert Data Field
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Pkt avg")
.Orientation = xlDataField
.Position = 1
.Function = xlAverage
.NumberFormat = "0"
.Name = "pkt avg"
End With
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Number of apt")
.Orientation = xlDataField
.Position = 2
.Function = xlCount
.NumberFormat = "0"
.Name = "Apt num"
End With
End Sub

我在这里得到类型不匹配错误Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange).CreatePivotTable(TableDestination:=PSheet.Cells(2, 2),TableName:="SalesPivotTable")

Answer 1

尝试

Sub Macro1()
'
' Macro1 Macro
'
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
'On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Regions").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "Regions"
Application.DisplayAlerts = True
Set PSheet = Worksheets("Regions")
Set DSheet = Worksheets("Sheet1")
'Define Data Range
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)
'Set Pivot table Cache
Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange)
Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")
'Insert Row Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("ID")
.Orientation = xlRowField
.Position = 1
End With
'Insert Data Field
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Pkt avg")
.Orientation = xlDataField
.Position = 1
.Function = xlAverage
.NumberFormat = "0"
'.Name = "Pkt avg"
.Caption = "Pkt Avgs" '~~> can't use same name with field name
End With
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Number of apt")
.Orientation = xlDataField
.Position = 2
.Function = xlCount
.NumberFormat = "0"
'.Name = "Apt num"
.Caption = "Apt num"
End With
Application.DisplayAlerts = False
End Sub