VBA: Update Pivot Tables not working

swayp11

Board Regular
Joined
Apr 27, 2009
Messages
107
Office Version
  1. 365
Platform
  1. Windows
I can't for the life of me figure out how to update my pivot tables in my workbook with VBA.

I have this code that I've read works for Excel 2007 and greater (I'm running 2016):
Code:
wb_myDatabase.RefreshAll

I also try to loop through all of my tables on the worksheet:
Code:
For Each myPivot In wb_myDatabase.Worksheets("Summary").PivotTables
    myPivot.PivotCache.Refresh
    myPivot.RefreshTable
    myPivot.Update
Next myPivot

I also try to update them manually on the screen:
Code:
wb_myDatabase.Worksheets("Summary").PivotTables("PivotTable1").PivotCache.Refresh
wb_myDatabase.Worksheets("Summary").PivotTables("PivotTable1").RefreshTable
wb_myDatabase.Worksheets("Summary").PivotTables("PivotTable1").Update


wb_myDatabase.Worksheets("Summary").PivotTables("PivotTable4").PivotCache.Refresh
wb_myDatabase.Worksheets("Summary").PivotTables("PivotTable4").RefreshTable
wb_myDatabase.Worksheets("Summary").PivotTables("PivotTable4").Update

For each of the above, I started with just the 'refreshtable' method, but then read that it refreshes on the back-end and you need to update, which is why I added the 'update' method. I also added in the pivotcache.refresh to see if that can get it to work. I get no errors, just no update.

All of the above code runs back to back and it still won't update.

I then changed the PivotTable Options to update the table on workbook open, which does work, but not the way I want. I can see the values quickly change as soon as I open the workbook, but obviously none of the code above is doing anything. I need to reference this workbook from other programs/devices, so the update on workbook open option is not kicking in when I view this on my phone, for example.

Lastly, I tried to save, close, reopen, and resave the workbook at the end of my code:
Code:
wb_myDatabase.Close
Set wb_myDatabase = Nothing
Set wb_myDatabase = Workbooks.Open("myLocation\myFile.xlsm")
wb_myDatabase.Save
wb_myDatabase.Close
Set wb_myDatabase = Nothing

This also doesn't work. I think VBA is reopening and closing, but without a user doing it manually, the PivotTable option of refreshing on open isn't firing.

Anyone have any idea how to fix this?
Thanks!
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Watch MrExcel Video

Forum statistics

Threads
1,114,521
Messages
5,548,544
Members
410,847
Latest member
shaun32
Top