none
Excel 2010 Pivot Table VBA Not Refreshing Table

    Question

  • 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
    Wednesday, December 07, 2011 6:36 PM

Answers

  • 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
    Wednesday, December 07, 2011 7:46 PM

All replies

  • 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
    Wednesday, December 07, 2011 7:46 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.

    Friday, October 18, 2013 6:58 AM