• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Code Waiting for Pivot Table Refresh


I have a code running that generates a save file for each department that in our organization. Somewhere toward the middle, I would like to have a Summary Pivot Table to be refreshed before saving and other steps. How do I get the code to wait for the refresh to complete before continuing the steps? I tried a couple fixes with no luck.
>>> use code - tags <<<
Private Sub gendivfiles_Click()

    Dim rng As Range
    Dim c As Range
    Dim counter As Integer
    Dim xlWS As Excel.Worksheet
    Dim xlRange As Excel.Range
    Set xlWS = Worksheets("Labor BPW")
    Set xlRange = xlWS.Range("C3")

    Set rng = Worksheets("controls").Range("H2#")

    counter = 0

    For Each c In rng.Cells

    xlRange.PasteSpecial Paste:=xlPasteValues
    counter = counter + 1

    [RIGHT HERE IS WHERE I WANT MY PIVOT TABLE TO REFRESH BEFORE CONTINUING] It is a specific table called summary_pivot
'Protect workbook
    Sheets("guidance").Protect Password:="bpadreamteam"
    Sheets("Labor BPW").Protect Password:="bpadreamteam"
    Sheets("Proration Calculator").Protect Password:="bpadreamteam"
    Sheets("Additional Pay BPW").Protect Password:="bpadreamteam"
    Sheets("BPA Error Report").Protect Password:="bpadreamteam"
'Make appropriate sheets very hidden
    Sheets("controls").Visible = xlVeryHidden
    Sheets("fte").Visible = xlVeryHidden
    Sheets("rates_actual").Visible = xlVeryHidden
    Sheets("rates_budget").Visible = xlVeryHidden
    Sheets("query_pivot").Visible = xlVeryHidden
    Sheets("query_addtl pay").Visible = xlVeryHidden
'Save copy of workbook
    Application.DisplayAlerts = False
    cell = Range("B1").Value
    Fpath = "S:\Finance\BUDGET\UCDMC\2425\BPWs\Labor BPW for Divisions\"
    Fname = Fpath & cell & ".xlsm"
    ActiveWorkbook.SaveCopyAs Filename:=Fname

'Unhide all sheets
    For Each ws In Worksheets
        ws.Visible = True
'Unprotect all sheets
    For Each ws In Worksheets
    ws.Unprotect Password:="bpadreamteam"
        Next ws
    Next c

End Sub
Last edited by a moderator:
Not tested, but try:
With ActiveSheet.PivotTables("summary_pivot").PivotCache
  .BackgroundQuery = False
End With
You will probably have to change ActiveSheet to the sheet that the pivot is on.
That seems to have worked. Thanks!

To get around the ActiveSheet part, I rewrote a little (see below). I defined the sheet with the pivot as xlWS1.

>>> use code - tags <<<
    With xlWS1.PivotTables("summary_pivot").PivotCache
    .BackgroundQuery = False
    End With
Last edited by a moderator: