Pivot is not refreshing-CodePudding

There are 3 sheets in my excel file.

One (Qry Results) is Query, the next one (Qry) is importing the data from Query by formula, the last one (Results) has a Pivot table based on Qry data.

Qry Results needs to be refreshed in VBA. It is working fine. Qry data is udpated automatically by formula. If Qry data is 100, but I entered formula in 150 lines.

When I run VBA by step by step, the PIVOT is refresh properly. But when I run automatically, the pivot is not refreshing.


`Refresh Query    
    Sheets("Qry Results").Select
    Range("A4").Select
    ActiveWorkbook.RefreshAll


`Refresh Pivot
    Sheets("Result").Select
    Range("A12").Select
    ActiveWorkbook.RefreshAll
Or 
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

CodePudding user response：

You may have background refresh enabled in the Power Query. With this setting active, the Power Query starts to refresh, but the pivot table refresh is kicked off basically at the same time, before the new data is loaded by the Power Query.

To remedy that, you can disable background refresh on the Power Query. Right-click the Power Query in the side panel and select Properties. Untick the box.

Now the pivot table refresh will run only after the Power Query refresh has finished. -- And, by the way, there is no need to select anything before running ActiveWorkbook.RefreshAll and there is no need to refresh all twice.

CodePudding user response：

Maybe you could try something like this (in order to wait for the query refresh to happen):

Sub RefreshQuery()

   'Code to refresh query here

End Sub

Then, in the sheet with the query results:

Private Sub Worksheet_Change(ByVal Target As Range)

    'Code to update pivot here

End Sub