Good Afternoon!
I have a macro that I'm using to refresh several connections and pivot tables in a specific order so that the final tab has all the data refreshed correctly. (The data must be refreshed in a specific order with the dependencies I have built in)
It works great for the most part - however, I had some #N/A data flowing into my final tab. I found that one of the pivot tables was *not* refreshing even though it is commanded as such in the macro below. The line of code to refresh the pivot table I'm struggling with is: Sheets("Sales Pivot").PivotTables("PivotTable2").PivotCache.Refresh. After the macro runs, the data in final tab is flowing in #N/A because this pivot table is not refreshed with the new dataset. If I go into that tab and refresh that pivot table manually, the final tab data flows in properly, so I figured that it is simply not refreshing correctly within the macro.
I tried adding an additional refresh of this tab/table at the very end of the sub command to mimic what I do by refreshing it manually, but that still doesn't work.
Oddly enough, if I single out this line to it's own sub and run them separately, it works, but when it is embedded in this sub, it does not refresh. Additionally, if I create a separate sub and try to call that sub at the end of the original macro, it still doesn't work. For some reason this line of code just does not like this sub. Any thoughts/ideas what I am doing wrong?
I have made red and underlined below the two spots where this data is being refreshed in the macro.
Unfortunately even if I shared an attachment of this workbook, the data would not flow in because it is bringing in data from several other data sources.
Sub Refresh()
'ProdlineList Prodlines = Forecastability
ActiveWorkbook.Connections("Forecastability").Refresh
'SelectTab = Forecastability2
ActiveWorkbook.Connections("Forecastability2").Refresh
'SalesData = RangePlanning
ActiveWorkbook.Connections("RangePlanning").Refresh
'FcstData = RangePlanning1
ActiveWorkbook.Connections("RangePlanning1").Refresh
'SalesData Pivot
Sheets("Sales Pivot").PivotTables("PivotTable2").PivotCache.Refresh
'FcstPivot
Sheets("FcstPivot").PivotTables("PivotTable2").PivotCache.Refresh
'SkuStrategy = RangePlanning2
ActiveWorkbook.Connections("RangePlanning2").Refresh
'VolCalcProd = Forecastability3
ActiveWorkbook.Connections("Forecastability3").Refresh
'SalesDataProd = Forecastability4
ActiveWorkbook.Connections("Forecastability4").Refresh
'SalesPivotProd
Sheets("SalesPivotProd").PivotTables("PivotTable1").PivotCache.Refresh
'ProdlineListDate = Forecastability5
ActiveWorkbook.Connections("Forecastability5").Refresh
'VolCalc = Forecastability1
ActiveWorkbook.Connections("Forecastability1").Refresh
'St.Dev Sales = Forecastability12
ActiveWorkbook.Connections("Forecastability12").Refresh
'COV LCL = Forecastability 121
ActiveWorkbook.Connections("Forecastability121").Refresh
'COV UCL = Forecastability 1211
ActiveWorkbook.Connections("Forecastability1211").Refresh
'COVexclOutliers = Forecastability 122
ActiveWorkbook.Connections("Forecastability122").Refresh
'MAPEexclOutliers = Forecastability 1221
ActiveWorkbook.Connections("Forecastability1221").Refresh
'ErrLCL = Forecastability 12211
ActiveWorkbook.Connections("Forecastability12211").Refresh
'St.Dev Error = Forecastability 122111
ActiveWorkbook.Connections("Forecastability122111").Refresh
'Fcst Error = Forecastability 1221111
ActiveWorkbook.Connections("Forecastability1221111").Refresh
'ErrUCL = Forecastability 1221122
ActiveWorkbook.Connections("Forecastability122112").Refresh
'BiasExclOutliers = Forecastability 12212
ActiveWorkbook.Connections("Forecastability12212").Refresh
'MainTab is connection: Forecastability11
ActiveWorkbook.Connections("Forecastability11").Refresh
'PriorityReviewPivot
Sheets("PriorityReviewPivot").PivotTables("PivotTable2").PivotCache.Refresh
'PriorityRank
Sheets("PriorityRank").PivotTables("PivotTable1").PivotCache.Refresh
'ProdPlantPivot
Sheets("ProdPlantPivot").PivotTables("PivotTable2").PivotCache.Refresh
'MainTab is connection: Forecastability11
ActiveWorkbook.Connections("Forecastability11").Refresh
'SalesData Pivot
Sheets("Sales Pivot").PivotTables("PivotTable2").PivotCache.Refresh
End Sub
Hi,
Try again with DoEvents placed in the first line of code, maybe some processes are not completed when the next line is called.
Let us know if this makes any difference.
Catalin
DoEvents did not change anything. 🙁
I am still experiencing some sheets/connections that are not refreshing even though they are commanded to do so in the macro.
Any additional thoughts would be appreciated!!
Try
Sheets("Sales Pivot").PivotTables("PivotTable2").Update
Thank you for continuing to help me Catalin!
The .Update function did not work either.
I also tried .RefreshTabl and I'm in the process of researching additional options.
Please let me know if you have any other ideas!!
Thanks!
I did come across this: http://p2p.wrox.com/excel-vba/29037-cant-set-commandtext-property-if-cache-has-1-rpt.html
which could be a viable solution (I'm really not sure if it applies, but would like to test it...). However, where would I input this in the code? I tried a few spots and it bombed out every time with a runtime error.
Hi,
That does not seem to be your case.
How is data coming into your workbook? Hard to see the problem without seeing the structure, but you might be refreshing the pivot while new data is not loaded yet (if it's a sql connection, or a query table)
Make sure you disable background refresh for all connections, the code should not go to the next step until the previous connection refresh does its job.
