Automatically refresh a pivot table by macro commands

Question

How do I refresh a Pivot Table by clicking on the sheet tab (by writing macro commands)?

Thanks,

Danie_007

Answer 1

Right-click the sheet tab.

Select "View Code" from the context menu.

Copy the following code into the worksheet's code module:

Private Sub Worksheet_Activate()

    Dim pvt As PivotTable

    For Each pvt In Me.PivotTables

        pvt.RefreshTable

    Next pvt

End Sub

Switch back to Excel. Make sure that you enable macros when you open the workbook.

Answer 2

Is the worksheet protected?

Answer 3

Hi Hans,

I don't know why this method works for one but not for another. I get the following error when I'm changing something:

'Run Time Error: 1004: RefreshTable method of PivotTable Class failed'

Though I have used exactly the same code you suggested, it still does not work. Can you please help me on this? I'm working on Excel 2013.

~Regards,

Maneesh

Answer 4

Right-click the sheet tab.

Select "View Code" from the context menu.

Copy the following code into the worksheet's code module:

 

Private Sub Worksheet_Activate()

    Dim pvt As PivotTable

    For Each pvt In Me.PivotTables

        pvt.RefreshTable

    Next pvt

End Sub

 

Switch back to Excel. Make sure that you enable macros when you open the workbook.

Thanks so much for your answer.  It works perfectly.