excel - VBA - Wait for formulae dependant on PivotTable to update before proceeding -

as per title, looking wait formulae dependent on pivottable update before proceeding.

the set (in excel 2010 workbook):

1. pivottable constructed
   • one xldatafield (unchanging),
   • several xlrowfields (unchanging), ,
   • one xlpagefield (for changing via vba, increase speed of data retrieval)
2. two-dimensional range (columns x rows) populated using formulae (including getpivotdata derived above pivottable)

processing (via vba):

3. the above mentioned xlpagefield changed (expecting values in above mentioned range updated)
4. two-dimensional range “read” variant further processing via vba

the process falling on between steps 3 , 4. range values not updated before being read variant. pivottable not updating, formulae dependant on pivottable not updating, or both.

solutions attempted date (from various stackoverlow threads or external websites):

• pivottables("*name*").pivotcache.backgroundq‌​uery = false (getting excel vba wait external workbook links refresh before continuing). results in run-time error '1004'.
• activeworkbook.refreshall doevents (wait until activeworkbook.refreshall finishes - vba) no impact.
• and mix of:
  • thisworkbook.refreshall doevents
  • pivottables("*name*").refreshtable doevents
  • application.calculate doevents
  • do until application.calculationstate = xldone doevents loop (wait until application.calculate has finished)

any guidance appreciated.

requested code (truncated)

dim allocationrange variant dim switchhistory pivottable  sub main()     initialisepublicvariables      'for each member         getmemberswitchhistory         dim allocations new dictionary: set allocations = getdictionary("allocations")     // further processes     'next member end sub  private sub initialisepublicvariables()     set switchhistory = sheets("all switches (clean)").pivottables("switchhistory")     allocationrange = range("allocationrange") end sub  private sub getmemberswitchhistory()     dim memberaccountnumber string: memberaccountnumber = range("memberaccountnumber").value     switchhistory.pivotfields("member account number").currentpage = memberaccountnumber     switchhistory.refreshtable     doevents end sub 

regards shannon

can refactor code can use pivottableupdate event on worksheet operate on pivot after updated?

private sub worksheet_pivottableupdate(byval target pivottable)    debug.print "pivot table updated - stuff..."  end sub 

you can use pivtotablechangesync event (this may more appropriate discerns changes more).

private sub worksheet_pivottablechangesync(byval target pivottable)    debug.print "pivot table change sync..."  end sub 

more information on event can found here:- https://msdn.microsoft.com/en-us/library/office/ff838251.aspx