运行时错误1004无法获得工作表类的可透视属性

Question

我正在使用宏来创建一个枢轴表，并且正在遇到运行时错误1004，而不知道为什么。所有的帮助将不胜感激

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "CTQ_Weekly_Attendance!R1C1:R1000C12", Version:=xlPivotTableVersion12). _
    CreatePivotTable TableDestination:="", TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion12
Sheets("By_Title_&_Level").Activate
Cells(1, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Title")
    .Orientation = xlRowField
    .Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Level_")
    .Orientation = xlRowField
    .Position = 2
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
    "PivotTable2").PivotFields("Picked_Up"), "Sum of Picked_Up", xlSum
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
    "PivotTable2").PivotFields("Attended"), "Sum of Attended", xlSum
ActiveWorkbook.ShowPivotTableFieldList = True

Answer 1

在枢轴表方面，宏记录器不是很好 - 您通常希望将枢轴缓存和枢轴表创建分为单独的步骤，并且您的代码将始终从使用几个变量中受益：

Sub Pivot()

    Dim wb As Workbook, pc As PivotCache, pt As PivotTable
    Dim wsData As Worksheet, wsPivot As Worksheet
    
    Set wb = ActiveWorkbook
    Set wsPivot = wb.Worksheets("By_Title_&_Level")
    Set wsData = wb.Worksheets("CTQ_Weekly_Attendance")
    
    Set pc = wb.PivotCaches.Create(SourceType:=xlDatabase, _
                              SourceData:=wsData.Range("A1:L1000"), _
                              Version:=xlPivotTableVersion12)
              
    Set pt = pc.CreatePivotTable(TableDestination:=wsPivot.Range("B3"), _
                                 TableName:="PivotTable2", _
                                 DefaultVersion:=xlPivotTableVersion12)

    With pt.PivotFields("Title")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    With pt.PivotFields("Level_")
        .Orientation = xlRowField
        .Position = 2
    End With
    
    pt.AddDataField pt.PivotFields("Picked_Up"), "Sum of Picked_Up", xlSum
    pt.AddDataField pt.PivotFields("Attended"), "Sum of Attended", xlSum
    wb.ShowPivotTableFieldList = True

End Sub