创建数据透视表时出错："Application Defined or object Defined Error."

Question

我正在创建一个数据透视表。我有三张纸，我正在工作表中创建数据透视表CAT_Pivot，并在准备表中使用数据。

我能够实现我的目标，但在执行结束时出现运行时错误。运行时错误状态

应用程序定义或对象定义错误。

除此之外，如何计算数据透视表中的值？我用了.function = xlcount，我没有成功。

这是我的代码：

Sub AutoPivot()
Dim PvtCache            As PivotCache
Dim PvtTbl              As PivotTable
Dim PvtSht              As Worksheet
' set Pivot Cache for Pivot Table
' Your range is static, there are ways to refer to a dynamic range
Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Preparation sheet!R1C1:R1048576C8")
' set the Pivot table's sheet
Set PvtSht = Worksheets("CAT_Pivot")
' add this line in case the Pivot table doesn't exit >> first time running this Macro
On Error Resume Next
Set PvtTbl = PvtSht.PivotTables("PivotTable1") ' check if "PivotTable7" Pivot Table already created (in past runs of this Macro)
On Error GoTo 0
If PvtTbl Is Nothing Then ' Pivot table object is nothing >> create it
' create a new Pivot Table in "PivotTable4" sheet
Set PvtTbl = PvtSht.PivotTables.Add(PivotCache:=PvtCache, TableDestination:=PvtSht.Range("A3"), TableName:="PivotTable1")
With PvtTbl
With .PivotFields("Category")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Colour")
.Orientation = xlColumnField
.Position = 1
End With
With .PivotFields("Category")
.PivotItems("DG-035583").Visible = False
.PivotItems("DG-048917").Visible = False
.PivotItems("DG-Series").Visible = False
.PivotItems("gn").Visible = False
.PivotItems("yl").Visible = False
.PivotItems("(blank)").Visible = False
End With
With .PivotFields("Colour")
.PivotItems("(blank)").Visible = False
End With
End With
Else
' just refresh the Pivot cache with the updated Range
PvtTbl.ChangePivotCache PvtCache
PvtTbl.RefreshTable
End If
End Sub

Answer 1

问题是您正在尝试编写嵌套语句。尝试从头开始重写代码，而不要一次使用多个with。

例如，这部分不好：

With PvtTbl
With .PivotFields("Category")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Colour")
.Orientation = xlColumnField
.Position = 1
End With
With .PivotFields("Category")
.PivotItems("DG-035583").Visible = False
.PivotItems("DG-048917").Visible = False
.PivotItems("DG-Series").Visible = False
.PivotItems("gn").Visible = False
.PivotItems("yl").Visible = False
.PivotItems("(blank)").Visible = False
End With
With .PivotFields("Colour")
.PivotItems("(blank)").Visible = False
End With
End With

你可以这样重写它：

With PvtTbl.PivotFields("Category")
.Orientation = xlRowField
.Position = 1
End With
With PvtTbl.PivotFields("Colour")
.Orientation = xlColumnField
.Position = 1
End With

嵌套的引用 if - 嵌套与语句层次结构

干杯！

编辑：实际上嵌套，如果不是那么糟糕，它甚至可以工作。但是，这仍然有点令人困惑。