Update Pivot Table Data Source with VBA

[Jones1413]

Hello -- I have the below VBA code to update three different pivot tables in my Workbook. All of the Pivot Tables use the same data source from the same worksheet. The rows of data change each time I update the source data so I need the pivot table to update automatically when I run the below code. I keep receiving an error.

Sub Update_Pivot()

'Update source range for Pivot Tables

    Dim pt As PivotTable
    Dim pc As PivotCache
    Dim source_data As Range

 lstrow = Cells(Rows.Count, 1).End(xlUp).Row
 lstcol = Cells(1, Columns.Count).End(xlToLeft).Column

 Set source_data = Range(Cells(1, 1), Cells(lstrow, lstcol))

Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, SourceData:=source_data)
Set pt = Sheet2.PivotTables("PivotTable1")
Set pt2 = Sheet3.PivotTables("PivotTable2")
Set pt3 = Sheet4.PivotTables("PivotTable3")

pt.ChangePivotCache pc
pt2.ChangePivotCache pc
pt3.ChangePivotCache pc
   
'Refresh Pivot Tables

ActiveWorkbook.RefreshAll
    
End Sub

 

[Domenic]

Once you've assigned the pivot cache to your first pivot table, assign the other pivot tables the pivot cache by index number...

pt.ChangePivotCache pc
pt2.CacheIndex = pt.CacheIndex
pt3.CacheIndex = pt.CacheIndex

Actually, it can be re-written as follows...

With pt
    .ChangePivotCache pc
    pt2.CacheIndex = .CacheIndex
    pt3.CacheIndex = .CacheIndex
End With

Also, once the pivot caches are assigned, the pivot tables are automatically refreshed. And so there's no need to refresh them with ActiveWorkbook.RefreshAll.

Alternatively, though, there's an easier way to do the same thing without all of that code. First convert your data into a Table (Ribbon >> Insert >> Tables >> Table). As such your range will automatically adjust as rows are added/removed. Then whenever your table is updated, you simply need to refresh the pivot cache with the following one line of code, and all of the pivot tables will automatically refresh...

ThisWorkbook.PivotCaches(1).Refresh

Hope this helps!

 

[Jones1413]

Once you've assigned the pivot cache to your first pivot table, assign the other pivot tables the pivot cache by index number...

pt.ChangePivotCache pc
pt2.CacheIndex = pt.CacheIndex
pt3.CacheIndex = pt.CacheIndex

Actually, it can be re-written as follows...

With pt
    .ChangePivotCache pc
    pt2.CacheIndex = .CacheIndex
    pt3.CacheIndex = .CacheIndex
End With

Also, once the pivot caches are assigned, the pivot tables are automatically refreshed. And so there's no need to refresh them with ActiveWorkbook.RefreshAll.

Alternatively, though, there's an easier way to do the same thing without all of that code. First convert your data into a Table (Ribbon >> Insert >> Tables >> Table). As such your range will automatically adjust as rows are added/removed. Then whenever your table is updated, you simply need to refresh the pivot cache with the following one line of code, and all of the pivot tables will automatically refresh...

ThisWorkbook.PivotCaches(1).Refresh

Hope this helps!

I am still getting an error when I run the Macro. I've attached a screen shot. I have ensured the third pivot table is named PivotTable 3 as well. Any suggestions?

 

[Domenic]

It looks like you have not attached a screen shot. In any case, can you post your amended code within code tags like you did in your original post, and then specify which error you're getting, and on which line?

 

[Jones1413]

It looks like you have not attached a screen shot. In any case, can you post your amended code within code tags like you did in your original post, and then specify which error you're getting, and on which line?

I receive a Run-time error '1004': Method 'PivotTables' of object'_Worksheet' failed.

The error is on the line "Set pt3 = Sheet.4.PivotTables("PivotTable3")

Sub Update_Pivot()

'Update source range for Pivot Tables

    Dim pt As PivotTable
    Dim pc As PivotCache
    Dim source_data As Range

 lstrow = Cells(Rows.Count, 1).End(xlUp).Row
 lstcol = Cells(1, Columns.Count).End(xlToLeft).Column

 Set source_data = Range(Cells(1, 1), Cells(lstrow, lstcol))


 Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, SourceData:=source_data)
Set pt = Sheet2.PivotTables("PivotTable1")
Set pt2 = Sheet3.PivotTables("PivotTable2")
Set pt3 = Sheet4.PivotTables("PivotTable3")

pt.ChangePivotCache pc
pt2.CacheIndex = pt.CacheIndex
pt3.ChangePivotCache pc

'Refresh Pivot Tables

    ActiveWorkbook.RefreshAll
    
 Range("A1").Select
    
End Sub

 

[Domenic]

The error suggests that Sheet4 does not contained a pivot table named "PivotTable3". If in fact it does contain one with that name, check that the spelling is correct. Or, if you're actually referring to a worksheet named "Sheet4" instead of a worksheet with the code name Sheet4, replace...

Set pt3 = Sheet4.PivotTables("PivotTable3")

with

Set pt3 = Worksheets("Sheet4").PivotTables("PivotTable3")

Also, you'll need to replace...

pt3.ChangePivotCache pc

with

pt3.CacheIndex = pt.CacheIndex

By the way, it's considered good programming practice to declare all your variables. It can also catch errors, such as spelling mistakes.