Refresh Pivot table

[Guinaba]

Hi guys,

I am using the code below to refresh a pivot table, which is in another worksheet but in the same workbook, not sure what I am missing because it only works if I run it within Sales worksheet. I thought that using the whole object's hierarchy that would work.

Sub RefreshSales()

    ActiveWorkbook.Sheets("Sales").PivotTables(1).PivotCache.Refresh
    ActiveWorkbook.Sheets("Sales").PivotTables(1).RefreshTable
    MsgBox "Refresh completed", vbInformation
  
End Sub

 

[Alex Blakenburg]

@DanteAmor - just curious as to why did you think that might help ?

Unless I am mistaken the only thing that does is to change whether items deleted still show in the filter dropdowns, with "None" resetting the list to only display current items.

 

[Guinaba]

Did you by any chance rename the button after you added the code ?
Either go into the buttons properties or if you have only a few objects select the button and hit Alt+F10 to view the object property pane in Excel.

Thanks @Alex Blakenburg! Silly mistake from my side! Just had assing the incorrect sub, so when I was pushing the button to refresh sales, actually I was refresh the forecast !! All good now!

 

[Guinaba]

Try this:

Sub RefreshSales()
    With ActiveWorkbook.Sheets("Sales").PivotTables(1)
      .PivotCache.MissingItemsLimit = xlMissingItemsNone
      .PivotCache.Refresh
      .RefreshTable
    End With
    MsgBox "Refresh completed", vbInformation
End Sub

Thanks @DanteAmor! That is a good piece of code to refresh pivot tables!!

 

[Alex Blakenburg]

Thanks @Alex Blakenburg! Silly mistake from my side! Just had assing the incorrect sub, so when I was pushing the button to refresh sales, actually I was refresh the forecast !! All good now!

Thanks for closing the loop on this. Happy to help.