Macro to refresh all Pivot tables in EXCEL 2013

[James Hodgson]

Hi All

Hopefully this is simple.

I have a large number of pivot tables that my lookups use. I want to create a button to update all so that I don't forget any.

I used this macro code which I found on this website (MR Excel):

Sub RefreshAll()
Dim pvt As PivotTable
Dim sh As Worksheet

For Each sh In Worksheets
For Each pvt In sh.PivotTables
pvt.RefreshTable
Next pvt
Next sh

End Sub

I created the button. I linked the Macro to the button. When I click the button I get the following error:

Run-time error 1004 - the pivot table field name is not valid. To create a pivot table report, you must use data that is organized as a list with labelled columns. If you are changing the name of a pivot table field, you must type a new name for the field.

When I run the debug - it tells me that the code line causing issue is "pvt.RefreshTable"

Any help would be appreciated.

Thanks
James

 

[vaskov17]

Pivot tables use the top row as field names. One way you can get this error is if a cell on the top row is blank and you are using that column in a PivotTable.