locked
Refresh pivot table question RRS feed

  • Question

  • Dear all, could you please help me in such a problem: i have Excel 2003 file with macros. Also in this file there are two pivot table. I'd like in first macro auto refresh all pivot tables (let us call this macro Macro1), in the second macro copy data from refreshed pivot table to the another sheet of this file (let us call this macro Macro2). File contains Workbook_WindowActivate subprogram.

    Private Sub Workbook_WindowActivate(ByVal Wn As Window)

      application.Run Macro:=("Macro1")

      application.Run Macro:=("Macro2")

    End Sub

     

    The problem is Macro1 start refreshing all pivot tables but Macro2 doesn't wait while refreshing process will be finished and copies data which are not ready. So not all the data are copied to another sheet. How can i ask Excel to wait while refreshing will be finished?

    Thank you!

    Thursday, October 28, 2010 9:00 AM

Answers

  • You can try to set the BackGroundQuery property to False to let the code wait until everything's refreshed -

    Worksheets(1).PivotTables("PivotTable1").PivotCache.BackgroundQuery = False
    • Marked as answer by Anita_address Thursday, February 3, 2011 7:28 AM
    Thursday, February 3, 2011 12:30 AM

All replies

  • You can try to set the BackGroundQuery property to False to let the code wait until everything's refreshed -

    Worksheets(1).PivotTables("PivotTable1").PivotCache.BackgroundQuery = False
    • Marked as answer by Anita_address Thursday, February 3, 2011 7:28 AM
    Thursday, February 3, 2011 12:30 AM
  • Correct!

    Thank you! :)

    Thursday, February 3, 2011 7:29 AM