Excel VBA 更改数据透视表的数据源
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31277854/
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
Excel VBA Changing Data Source for Pivot Table
提问by Meghan Anderson
Okay, I have sifted through tons of posts and cannot get my code to run. I am trying to create a reconciliation report, which I have running just fine. The problem comes when I am trying to take each month's report and paste it into a master reconciliation file. I have everything working perfectly, except I cannot for the life of me figure out WHY my pivot table source won't change.
好的，我已经筛选了大量帖子，但无法运行我的代码。我正在尝试创建一个对帐报告，我运行得很好。当我尝试获取每个月的报告并将其粘贴到主对帐文件中时，问题就出现了。我让一切正常工作，除了我一生都无法弄清楚为什么我的数据透视表源不会改变。
The error I am getting with the code as is:
我在代码中遇到的错误是：
Run-time error '1004': Application-defined or object-defined error
运行时错误“1004”：应用程序定义或对象定义错误
Any suggestions? :-\
有什么建议？:-\
Dim SrcData As String
Dim PivTbl As PivotTable
SrcData = ActiveSheet.Name & "!" & Range("$A:$H$" & LastRow).Address(ReferenceStyle:=xlR1C1)
On Error Resume Next
Set PivTbl = Sheets("Report").PivotTables("ReportPivot")
On Error GoTo 0
If PivTbl Is Nothing Then
'create pivot
Else
ActiveWorkbook.PivotTables(PivTbl).ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData, _
Version:=xlPivotTableVersion15)
PivTbl.RefreshTable
End If
采纳答案by Daniel M?ller
Since the problem is here:
由于问题在这里：
ActiveWorkbook.PivotTables(PivTbl).ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData, _
Version:=xlPivotTableVersion15)
I guess you cannot get a pivot table passing the table itself as argument: .PivotTables(PivTbl)
我想你不能得到一个数据透视表，将表本身作为参数传递： .PivotTables(PivTbl)
PivTblis already the table you want.
PivTbl已经是你想要的表了。
PivTbl.ChangePivotCache.......
