Excel VBA更改数据透视表的数据源 [英] Excel VBA Changing Data Source for Pivot Table
问题描述
好的,我筛选了成千上万的帖子,但无法运行我的代码.我正在尝试创建对帐报告,但我运行得很好.当我尝试获取每个月的报表并将其粘贴到主对帐文件时,就会出现问题.我一切正常,但我无法终生弄清楚为什么透视表源不会更改.
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:
运行时错误'1004':应用程序定义或对象定义的错误
Run-time error '1004': Application-defined or object-defined error
有什么建议吗? :-\
Any suggestions? :-\
Dim SrcData As String
Dim PivTbl As PivotTable
SrcData = ActiveSheet.Name & "!" & Range("$A$1:$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
推荐答案
问题出在这里:
ActiveWorkbook.PivotTables(PivTbl).ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData, _
Version:=xlPivotTableVersion15)
我想您无法获得将表本身作为参数传递的数据透视表:.PivotTables(PivTbl)
I guess you cannot get a pivot table passing the table itself as argument: .PivotTables(PivTbl)
PivTbl
已经是您想要的表.
PivTbl
is already the table you want.
PivTbl.ChangePivotCache.......
这篇关于Excel VBA更改数据透视表的数据源的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!