在Excel中使用VBA创建数据透视表时,键入不匹配错误 [英] Type mismatch error when creating a pivot table in Excel with VBA
问题描述
我正在尝试将宏放在一起,这将使用活动工作表中的数据创建一个简单的数据透视表。当我尝试运行它,我收到类型不匹配错误。当我启动调试器时,第一部分突出显示:ActiveWorkbook.PivotCaches通过xlPivotTableVersion10。最初,TableDestination是空白的,我认为这可能是问题,但添加目的地后,我仍然会收到相同的错误。
I'm trying to put a macro together that will make a simple pivot table using the data from an active worksheet. When I try to run it, I receive a type mismatch error. When I start the debugger, the first section is highlighted: ActiveWorkbook.PivotCaches through xlPivotTableVersion10. Initially, the TableDestination was blank and I thought that might be the problem, but after adding a destination I still get the same error.
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=ActiveSheet.UsedRange).CreatePivotTable TableDestination:="Sheet1!R3C1", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(1, 1)
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Program Name")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Dollars Awarded"), "Sum of Dollars Awarded", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Dept Head")
.Orientation = xlRowField
.Position = 1
End With
推荐答案
这对我有用(XL2007):
This worked for me (XL2007):
Sub Tester()
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=ActiveSheet.UsedRange)
.CreatePivotTable TableDestination:="Sheet1!R3C1", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
End With
With Sheet1.PivotTables("PivotTable1")
.PivotFields("Dept Head").Orientation = xlColumnField
.PivotFields("Program Name").Orientation = xlRowField
.AddDataField .PivotFields("Cost"), "Sum of cost", xlSum
End With
End Sub
确保您还没有存在冲突的转发缓存/表。
Make sure you don't already have an existing conflicting pivot cache/table.
这篇关于在Excel中使用VBA创建数据透视表时,键入不匹配错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!