StripeReportsFolder = ThisWorkbook.Path & "\Final Reports" 'save new workbook in Final Reports Folder Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ This way, both the Power Query table and the pivot table get refreshed at once, without having to click the button Refresh All twice.Ĭopy to Clipboard Sub SaveTransformedReport()ĪctiveWorkbook.Connections("Query - Data").Refresh If the property “Enable Background Refresh” is disabled, the system will refresh the Pivot Table query first, and when the table is updated the pivot table gets also updated with the new information. When the property “Enable Background Refresh” is disabled: Then, when we click Refresh All a second time, since we already had the Power Query table updated, this time the pivot table got refreshed with the most current data.
Because the two processes start around the same time, the pivot table is not updated correctly because the data it has access to at the beginning of the refreshing process has not been updated yet with the most current information. Therefore, the pivot table is being refreshed at the same time as the Power Query table is. However, the system is not waiting for the Power Query table to be finished with the data updating before starting with the pivot table’s updating process. The pivot table shows a summary of the data in the Power Query table.
When we have the “Enable Background Refresh” property enabled, we are telling the system that every time we ask it to refresh the Power Query query, we authorize the system to move on with other tasks, without needing to wait for that query refreshing process to be complete.