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

 

[alansidman]

How about

ActiveWorkbook.RefreshAll

 

[Guinaba]

How about

ActiveWorkbook.RefreshAll

Thanks @alansidman but there are some pivot table that I refresh in specific days of the week, so I would avoid refresh all. Why are you using this option?

 

[Alex Blakenburg]

@Guinaba - It works fine for me.

Do you have it in a standard module (not in a worksheet module) ?
How are you running the code ?
Is there only 1 pivot table on the Sales Tab ?

 

[alansidman]

Why are you using this option?

I use this VBA to refresh Power Query tables.

 

[Guinaba]

@Guinaba - It works fine for me.

Do you have it in a standard module (not in a worksheet module) ?
How are you running the code ?
Is there only 1 pivot table on the Sales Tab ?

Hi @Alex Blakenburg,

I am using it in a standard module (below pic) and I run the code calling this Sub from a button in another worksheet called "Summary" and yes there is only one pivot table in there. I've tried adding the name as well "ptSales" but didn't work. Really weird!

 

[Guinaba]

Hi @Alex Blakenburg,

I am using it in a standard module (below pic) and I run the code calling this Sub from a button in another worksheet called "Summary" and yes there is only one pivot table in there. I've tried adding the name as well "ptSales" but didn't work. Really weird!

Hi @Alex Blakenburg, just realized that if I run the sub from other sheet whithout using the button (code below), it works. So not sure why the call method doesn't

Private Sub CommandButton2_Click()
   Call RefreshSales
End Sub

 

[Alex Blakenburg]

I was about to address that very point.

Can you put a msgbox as the first line of the code and then hit the run button.
(or a debug.print "Executing RefreshSales" OR a breakpoint)

My guess is that your button is not calling the code.

 

[Alex Blakenburg]

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.

 

[DanteAmor]

Try this:

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