My company recently upgraded from Excel 2003 to 2010. I had VBA written to take source data and convert it into a number of Pivot Tables on a number of worksheets. It has been working fine for years. After upgrading to 2010 the VBA crashed. I tracked it down to the fact that when my code was making changes to the Pivot Tables (changing fields, filters, etc...) the pivot table on the worksheet had no data, but the fields were there. I can manually go to the pivot table and manually refresh and all the data comes in.

So I tried adding the VBA code to refresh the pivot table, but the pivot tables will not refresh with data.

I tried:

ActiveSheet.PivotTables("WO Pivot").RefreshTable

and

ActiveWorkbook.RefreshAll

And these did not work.

I also tried recording a macro for the manual steps to refresh and got:

 ActiveSheet.PivotTables("WO Pivot").PivotCache.Refresh

This does not work either.

The PivotTable name is correct, but I tried using the number as well, and the name works for other code manipulating the the pivot table.

e.g.:

With ActiveSheet.PivotTables("WOPivot").PivotFields("Task Title")

      .Orientation = xlRowField .Position = 2

      .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _False, False)

End

With Why isn't this working? Is there another way to refresh pivot table data in 2010?

Thanks. P.S. I've tried formating this so it is readable, but it comes out garbled. Hope this looks better.

• Edited by FSJTurtle Wednesday, December 07, 2011 6:39 PM

I eventually figured this one out myself. All the "Refresh" methods don't actually update the worksheet view. I needed to use the "Update" method to actually get the data in the worksheet to refresh.

So: ActiveSheet.PivotTables("WOPivot").Update

Works!

• Marked as answer by FSJTurtle Wednesday, December 07, 2011 7:56 PM

The solution above didn't work for me, but the following did the trick:

ActiveSheet.PivotTables("WOPivot").PivotCache.Refresh

By the way, I identified it by recording a macro, then going on the Pivot Table that needed refreshing and pressing F9 to refresh it. The line of VBA code above was the result.

Cheers,
Marco.

I have the same issue and have tried both of those solutions and nothing is working!

My pivots will refresh manually and they will refresh if I step through my code in debug mode, but will NOT refresh with my macro alone.

I have done this so many times in other applications/macros I can't understand why it isn't working.

Could there be some obscure setting I activated that is buried somewhere and causing this?

Help would be appreciated!

Thanks,

Lisa

You probably long gave up on this but I had the same issue. My pivot table was using another table as a data source and that table had the data connection. I noticed if I ran the macro twice it would correctly update the second time.

My solution was to make a second identical data source for the pivot table. You can do this by selecting data on the ribbon and Existing Connections. From there you can pick the old connection and select pivot table. It will create a duplicate connection which you can then rename in the connection manager. Then in your update macro load update this connection as well. You'll also want to refresh the pivot cache for your table.