vba 录制的宏给出运行时错误 5
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18914096/
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
recorded macro gives runtime error 5
提问by JUNGE
I'm new with VBA. I recorded a macro an d wanted to edit it. after recording it, I wanted to run it one time. But when I did, it returned the Runtime Error 5.
我是 VBA 新手。我录制了一个宏并想对其进行编辑。录制后，我想运行一次。但是当我这样做时，它返回了运行时错误 5。
The Macro should take and from a sheet and add it into a pivottable in another sheet.
宏应该从工作表中取出并添加到另一个工作表中的数据透视表中。
So this is the code, where the error is based.
所以这是错误所在的代码。
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"sourcetable!R1C1:R1048576C21", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Tabelle2!R3C1", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion14
thanks for your help
感谢您的帮助
采纳答案by Siddharth Rout
no it doesn't it's a new sheet only for this pivot @SiddharthRout – beginner 4 mins ago
不，这不是仅针对此支点的新工作表 @SiddharthRout – 初学者 4 分钟前
The simplest way to do is
最简单的方法是
With ActiveWorkbook
.Sheets("Tabelle2").Cells.Clear
.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"sourcetable!R1C1:R1048576C21", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Tabelle2!R3C1", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion14
End With
Also I noticed that you have source data defined till the row 1048576. Why so? A more perfect way would be to find the last row and then construct your range. For example
另外我注意到你有源数据定义到第 1048576 行。为什么会这样？更完美的方法是找到最后一行，然后构建您的范围。例如
Sub Sample()
Dim lRow As Long
With ActiveWorkbook
'~~> Find last row in sheet sourcetable
With .Sheets("sourcetable")
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
lRow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Else
lRow = 1
End If
End With
.Sheets("Tabelle2").Cells.Clear
.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:="sourcetable!R1C1:R" & _
lRow & _
"C21", _
Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Tabelle2!R3C1", _
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion14
End With
End Sub
回答by LS_???
First: does sourcetable and Tabelle2 worksheets exist?
第一：是否存在 sourcetable 和 Tabelle2 工作表？
Then, try it removing all optional meaningless arguments:
然后，尝试删除所有可选的无意义参数：
ActiveWorkbook.PivotCaches.Create(xlDatabase, "sourcetable!R1C1:R1048576C21") _
.CreatePivotTable ActiveWorkbook.Worksheets("Tabelle2").Range("R3C1")
