VBA Code Waiting for Pivot Table Refresh

[cpadilla0024]

Hi,

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.

 

[AlanSidman]

Show us the code with the refresh directions so that it can be analyzed.

 

[cpadilla0024]

>>> 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


    c.Copy
    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
    Next
   
'Unprotect all sheets
    For Each ws In Worksheets
    ws.Unprotect Password:="bpadreamteam"
        Next ws
   
    Next c


End Sub

 

[p45cal]

Not tested, but try:

With ActiveSheet.PivotTables("summary_pivot").PivotCache
  .BackgroundQuery = False
  .Refresh
End With

You will probably have to change ActiveSheet to the sheet that the pivot is on.

 

[cpadilla0024]

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
    .Refresh
    End With