vba 数据透视表创建的运行时错误 1004
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20365882/
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
Run time error 1004 for pivot table creation
提问by user1204868
I am trying to create a pivot table from a .csv file. But I am having a run time error 1004: Reference is not valid over at the ActiveWorkbook portion of the code.
我正在尝试从 .csv 文件创建数据透视表。但是我遇到了运行时错误 1004：在代码的 ActiveWorkbook 部分，引用无效。
Any suggestions for this?
对此有何建议？
My code is as follows
我的代码如下
Sub test()
Dim ws As Worksheet
Set ws = ActiveSheet
Sheets.Add
ws.Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
ws.Name & "R1C1:R101643C21", Version:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion10
End Sub
I did a Sheets.Add to add in new worksheet. I used a ws.Name as the name can be any name. Just an addition question, is it possible to change R1C1:R101643C21 to a varying range as the data may not be that big or small?
我做了一个 Sheets.Add 来添加新的工作表。我使用了 ws.Name 作为名称可以是任何名称。只是一个附加问题，是否可以将 R1C1:R101643C21 更改为不同的范围，因为数据可能不会那么大或那么小？
回答by sam092
Notice the !in SourceData. The missing !is causing the invalid reference
请注意!SourceData 中的 。缺失!导致无效引用
Sub test()
Dim ws As Worksheet
Set ws = ActiveSheet
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
ws.Name & "!R1C1:R11C2", Version:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion10
End Sub
For dynamic range, see this
有关动态范围，请参阅此
Sub test()
Dim ws As Worksheet
Dim rng As Range
Set ws = ActiveSheet
Set rng = ws.Range("A1:B10")
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
rng, Version:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion10
End Sub
Just a reminder, you are only creating a empty pivot table in this way
提醒一下，您只是以这种方式创建了一个空的数据透视表
