Hi,
Problem - I am trying to automatically update several pivot tables based on one data source. I've put the code into a Private sub Worksheet_Deactivate so it will refresh all PT's when I move from that page.
Here is the code I used which works great for one pivot table. However, as soon as I try to define names for the other pivot tables, it gives me a procedure call error.
Private Sub Worksheet_Deactivate()
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("PivotTable2")
'Set pt2 = Sheet3.PivotTables("PivotTable3") Commented out
pt.ChangePivotCache pc
'pt2.ChangePivotCache pc Commented out
End Sub
This code currently works for one pivot table but does not work for two pivot tables. I've commented out the pt2 lines, because this is where it would give me errors. I have dumbed my workbook down to two pivot tables off the same source but can't get it to work.
Any suggestions would be much appreciated. Thanks
Problem - I am trying to automatically update several pivot tables based on one data source. I've put the code into a Private sub Worksheet_Deactivate so it will refresh all PT's when I move from that page.
Here is the code I used which works great for one pivot table. However, as soon as I try to define names for the other pivot tables, it gives me a procedure call error.
Private Sub Worksheet_Deactivate()
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("PivotTable2")
'Set pt2 = Sheet3.PivotTables("PivotTable3") Commented out
pt.ChangePivotCache pc
'pt2.ChangePivotCache pc Commented out
End Sub
This code currently works for one pivot table but does not work for two pivot tables. I've commented out the pt2 lines, because this is where it would give me errors. I have dumbed my workbook down to two pivot tables off the same source but can't get it to work.
Any suggestions would be much appreciated. Thanks