Excel VBA Changing Data Source for Pivot Table
37,590
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)
PivTbl
is already the table you want.
PivTbl.ChangePivotCache.......
![Meghan Anderson](https://lh5.googleusercontent.com/-9s7_SO9RJ_U/AAAAAAAAAAI/AAAAAAAAALM/USHq16TXju4/photo.jpg?sz=256)
Author by
Meghan Anderson
Updated on September 13, 2020Comments
-
Meghan Anderson almost 4 years
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
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