title: Force VBA to wait until power pivot finishes refreshing
body: <p>I came across a very unusual error with VBA that I'm struggling with for two days now. I have a code that updates values to be shown in an Active-x dropdown list and then assigns them to the list using the <code>ListFillRange</code> property.
  Unfortunately every time I run it it generates an error. I presume the error is caused by running a piece of code on a power pivot that I'm refreshing before it completes the refresh. The error occurs in the 9th line of the lastRow function which selects a cell in the power pivot. The error no longer appears after I comment out the 5th line of the <code>Sub</code> which refreshes the pivot table.
  I guess the solution to this problem is forcing VBA to wait with going to the next step of the code until the refresh of the table is completed. I tried solving this problem by adding <code>DoEvents</code> and some other techniques that I've found online, but none of them worked. Any suggestions on tackling this problem would be highly appreciated. Thanks!</p>
  
  <pre><code>Sub updateList()
  Dim listRangeEnd As Long
  
  'Refresh pivot with all Promotion Weeks
  'Clear all filters
  Worksheets("Lookup").PivotTables("weeksList").ClearAllFilters
  'Refresh pivot
  Worksheets("Lookup").PivotTables("weeksList").RefreshTable
  
  
  'Set listFillRange for the list
  listRangeEnd = lastRow("Lookup", "D4")
  Worksheets("Inputs").list.ListFillRange = "Lookup!D4:D" &amp; listRangeEnd
  Worksheets("Inputs").list.Value = Worksheets("Lookup").Range("D4").Value
  
  End Sub
  
  
  Public Function lastRow(sheet As String, Cell As String)
  Dim Row As Long
  Dim currentSheet As String
  
  'Save the name of the currently selected sheet
  currentSheet = ActiveSheet.Name
  
  'Get the row number of the last non-empty cell in the column
  Worksheets(sheet).Select
  Worksheets(sheet).Range(Cell).Select
  If Selection.Offset(1, 0).Value = "" Then
      Row = ActiveCell.Row
  Else
      Row = Worksheets(sheet).Range(Cell).End(xlDown).Row
  End If
  
  'Go back to the previous sheet
  Worksheets(inputSheet).Select
  
  lastRow = Row
  
  End Function
  </code></pre>