CODEWITHSUNDEEP
excelvbapivot
Meghan Anderson
Meghan Anderson

Reputation: 75

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:

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

Upvotes: 4

Views: 39679

Answers (1)

Daniel Möller
Daniel Möller

Reputation: 86600

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.......

Upvotes: 4

Related Questions