vba 枢轴和运行时错误 1004：应用程序定义或对象定义错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23883640/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pivots and Run-time error 1004: Application-defined or object-defined error
提问by Simon
I'm quite new to macros and VBA coding and I'm trying to create a really simple macro that takes the data from a table with 33 columns and convert it to a pivot.
我对宏和 VBA 编码很陌生，我正在尝试创建一个非常简单的宏，该宏从具有 33 列的表中获取数据并将其转换为数据透视表。
Only the last 3 columns (31, 32, 33) contains numbers and I need that to appear on the pivot as I want to compare current month, last month and the movement from month on month.
只有最后 3 列（31、32、33）包含数字，我需要将其显示在枢轴上，因为我想比较当前月份、上个月和逐月的变动。
Here is my code so far:
到目前为止，这是我的代码：
Sub Macro6()
'
' Macro6 Macro
'
'
Dim shtSrc As Worksheet, shtDest As Worksheet
Dim pc As PivotCache
Set shtSrc = ActiveSheet
Set shtDest = shtSrc.Parent.Sheets.Add()
shtDest.Name = shtSrc.Name & "-Pivot"
Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=shtSrc.Range("A1").CurrentRegion)
pc.CreatePivotTable TableDestination:=shtDest.Range("A3"), _
TableName:="PivotTable1"
With shtDest.PivotTables("PivotTable1")
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With
Set shtDest = ActiveSheet
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Name")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(31), "Last month", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(32), "This month", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(33), "Movement", xlSum
End Sub
I get an error Run-time error 1004: Application-defined or object-defined error on this line:
我在这一行收到错误运行时错误 1004：应用程序定义或对象定义错误：
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(33), "Movement", xlSum
If I take away this line, the macro works fine but only creates 2 column. If I put in the line, it generates the error and does not create the 3rd column.
如果我拿走这条线，宏可以正常工作，但只创建 2 列。如果我放入该行，它会生成错误并且不会创建第三列。
Can anyone explain why?
谁能解释为什么？
采纳答案by Simon
OK. I think I figured it out, but it is quite strange that this is happening, is it a bug?
好的。我想我想通了，但发生这种情况很奇怪，这是一个错误吗？
The reason is that in my source table, the column heading says "Movement". If I change the caption to something other than the Column heading for example, by saying "MovementS", then the macro works.
原因是在我的源表中，列标题显示“移动”。例如，如果我将标题更改为列标题以外的其他内容，例如说“MovementS”，则宏将起作用。
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(33), "Movements", xlSum
回答by Jim
Another explanation is that the Row Label may have changed from Date Filter to a Label filter after a refresh. In particular I pull data in from a SQL query I need to convert my date range data type to date format by using text to columns after my refresh, then I will refresh my pivot table.
另一种解释是刷新后行标签可能已从日期过滤器更改为标签过滤器。特别是我从 SQL 查询中提取数据，我需要在刷新后通过使用文本到列将我的日期范围数据类型转换为日期格式，然后我将刷新我的数据透视表。
Check if your drop down for Row Label shows date filter instead of label filter to verify you have the correct format from your data source.
检查您的行标签下拉菜单是否显示日期过滤器而不是标签过滤器，以验证您的数据源格式是否正确。
