创建新数据透视表时应用程序定义的错误

Question

嗨，这个宏在一个 excel 工作表上工作，但是当我尝试在 excel 工作表的同一副本上使用它时，我遇到了应用程序定义/对象定义的错误。我正在尝试使用动态范围在新工作表上创建一个数据透视表。任何帮助将不胜感激修复我的代码

Sub CreatePivot()
Dim wsNew As Worksheet
Set wsNew = Sheets.Add
ActiveSheet.Name = "Pivot"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Report!" & Sheets("Report").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1), Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:=wsNew.Name & "!R3C1", TableName:="PivotTable4", DefaultVersion _ 
:=xlPivotTableVersion15

当我运行调试时，我收到错误：

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Report!"

Answer 1

尝试下面的动态代码来设置你的PivotTable，代码内部的解释作为注释：

Option Explicit
Sub CreatePivot()
Dim PvtCache            As PivotCache
Dim PvtTbl              As PivotTable
Dim wsNew               As Worksheet
Dim SrcRng              As Range
' add a new worksheet and name it "Pivot"
Set wsNew = Sheets.Add
ActiveSheet.Name = "Pivot"
' set the Source range of the Pivot Cache
Set SrcRng = Sheets("Report").Range("A1").CurrentRegion
' set Pivot Cache for Pivot Table
Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcRng.Address(True, True, xlA1, xlExternal))
' === for DEBUG Only ===
Debug.Print SrcRng.Address(True, True, xlA1, xlExternal)
' add this line in case the Pivot table doesn't exit >> first time running this Macro
On Error Resume Next
Set PvtTbl = wsNew.PivotTables("PivotTable4") ' check if "PivotTable4" 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 = wsNew.PivotTables.Add(PivotCache:=PvtCache, TableDestination:=wsNew.Range("A3"), TableName:="PivotTable4")
    With PvtTbl
        ' modify the resy of the Pivot-Table properties
    End With
Else
    ' just refresh the Pivot cache with the updated Range
    PvtTbl.ChangePivotCache PvtCache
    PvtTbl.RefreshTable
End If
End Sub