Multiple pivot tables refreshed with dynamic source data

dweston

New Member
Joined
Jul 21, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,854
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
If you only have one pt per sheet use sheetN.Pivottables(1) instead of a name.
If more than 1 per sheet: (2), (3) etc.
I will post a code tomorrow if i don't forget 😉.
 

dweston

New Member
Joined
Jul 21, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
If you only have one pt per sheet use sheetN.Pivottables(1) instead of a name.
If more than 1 per sheet: (2), (3) etc.
I will post a code tomorrow if i don't forget 😉.
Hi @bobsan42, i'm not exactly sure I understand what you mean. Would you be able to post some code? Thanks in advance!
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,854
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Sorry, was a busy day and I forgot. o_O
But it's over now. :cool:
I use this as a general code in a addin to update/refresh all pivot tables in the active workbook, regardless of the number of PTs on a sheet. I usually try to keep this to one, but sometimes I am tempted to break this rule :).
VBA Code:
Sub updateAllPivotTables()
    On Error GoTo ep
    Dim wb As Workbook: Set wb = ActiveWorkbook
    Dim wsh As Worksheet
    Dim pt As PivotTable
    Dim i As Integer
   
    For Each wsh In wb.Worksheets
        For Each pt In wsh.PivotTables
            pt.PivotCache.Refresh
            i = i + 1
        Next pt
    Next wsh
   
ep:
    MsgBox "workbook: " & wb.Name & vbCrLf & i & " Pivot tables refreshed.", vbOKOnly, glConstAppTitle
    Set wb = Nothing
    Set wsh = Nothing
    Set pt = Nothing
End Sub
Depending on your setup you may replace Activeworkbook with thisworkbook. Or adapt it to your needs.
BTW, are you trying to only refresh the PT or change the data source ?
 

dweston

New Member
Joined
Jul 21, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Sorry, was a busy day and I forgot. o_O
But it's over now. :cool:
I use this as a general code in a addin to update/refresh all pivot tables in the active workbook, regardless of the number of PTs on a sheet. I usually try to keep this to one, but sometimes I am tempted to break this rule :).
VBA Code:
Sub updateAllPivotTables()
    On Error GoTo ep
    Dim wb As Workbook: Set wb = ActiveWorkbook
    Dim wsh As Worksheet
    Dim pt As PivotTable
    Dim i As Integer
  
    For Each wsh In wb.Worksheets
        For Each pt In wsh.PivotTables
            pt.PivotCache.Refresh
            i = i + 1
        Next pt
    Next wsh
  
ep:
    MsgBox "workbook: " & wb.Name & vbCrLf & i & " Pivot tables refreshed.", vbOKOnly, glConstAppTitle
    Set wb = Nothing
    Set wsh = Nothing
    Set pt = Nothing
End Sub
Depending on your setup you may replace Activeworkbook with thisworkbook. Or adapt it to your needs.
BTW, are you trying to only refresh the PT or change the data source ?
Thanks, I will try this! Cheers
 

Mack23

New Member
Joined
Aug 11, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Hi, sorry for piggybacking off of this forum, but I have a similar issue that expands on this issue and solution. I have a table on one sheet that gets manually keyed entries. That data is then pushed to another worksheet that transforms the data table using a power query. That power query then is used in a pivot table on a third sheet. I have a button on the first sheet to refresh the power query and I would like to add the pivot table refresh portion to the button. The button does work but you have to hit the button twice to fully work. the first hit refreshes the power query and the next hit refreshes the pivot table. Here is the macro that I have and I know it has something to do with the worksheet refresh line just blanking on how to do this. Any help would be greatly appreciated!

Thank you,
Mack


Sub RefreshAll()

Worksheets("Allocation_Normalized").ListObjects("Table4_2").Refresh

On Error GoTo ep
Dim wb As Workbook: Set wb = ActiveWorkbook
Dim wsh As Worksheet
Dim pt As PivotTable
Dim i As Integer

For Each wsh In wb.Worksheets
For Each pt In wsh.PivotTables
pt.PivotCache.Refresh
i = i + 1
Next pt
Next wsh

ep:
MsgBox "Workbook: " & wb.Name & vbCrLf & i & "Pivot tables refreshed.", vbOKOnly, glConstAppTitle
Set wb = Nothing
Set wsh = Nothing
Set pt = Nothing

End Sub
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,854
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
The problem usually is that pq's are set to refresh in the background. So pivots are getting data before it's refreshed.
Turn background refresh off for powerqueries.
 

Mack23

New Member
Joined
Aug 11, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Yup, after I wrote that post I saw your other post and investigated that more. I disabled the auto refresh on the power query and it worked. I probably had half dozen different ways of code that would have worked if I just disabled the auto refresh.

Thank you very much,
Mack
 

Forum statistics

Threads
1,144,630
Messages
5,725,401
Members
422,623
Latest member
Dave52

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top