运行时错误1004无法获得工作表类的PivotTable属性
运行时错误1004无法获得工作表类的PivotTable属性
提问于 2022-05-31 20:01:45
我正在使用宏创建一个透视表，并遇到运行时错误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回答 1
Stack Overflow用户
回答已采纳
发布于 2022-05-31 20:17:09
对于pivot表来说，宏记录器不是很好--您通常希望将数据透视缓存和透视表创建分成不同的步骤，您的代码将始终受益于几个变量的使用：
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/72453551
复制相关文章
相似问题
腾讯云开发者
