I have a basic Dashboard that has one data table and four pivotcharts, NO pivot tables. Is there a way to modify the auto-updating macros in lesson 2.04 to work with pivot charts that are not linked to a pivot table? Maybe it is bad practice to not link a pivot table??? I was trying to save space by excluding them where "not needed". Thanks for any suggestions.
Sub RefreshPT()
Dim pt As PivotChart
' Dim pt As PivotTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotChart
pt.RefreshChart
'pt.RefreshTable
Next pt
Next ws
End Sub
Hi Michael,
A Pivot Chart not connected to a PivotTable because it's coming direct from Power Query or Power Pivot? If so, then you can record a macro that Refreshes All e.g.:
Sub RefreshAll() ' ' RefreshAll Macro ' ActiveWorkbook.RefreshAll End Sub
This will refresh all data connections/queries too.
Mynda
Hi Mynda,
I have a dedicated data workbook and I then use a query to update the data table in my dashboard workbook. I use power pivot to create the charts from the data table. I did write a macro to refresh as you suggested.
Thank you once again!
Michael
Hi Michael,
Just to be sure, the query should get the data from the external data workbook and load it directly to Power Pivot/the data model in the dashboard workbook. It shouldn't go to a table in an Excel worksheet in the dashboard workbook and then to Power Pivot.
Mynda
Hmmm...If I click Refresh All in the Dashboard workbook with a table data cell selected It updates the table data, and if I select a pivot chart in the Dashboard workbook on the dashboard sheet and click refresh data again the charts update.
I'm not sure how I set it up??? Can you direct me to the lesson(s) where making sure the data query is feeding into the data model in power pivot was covered? I have all 3 (PP, PQ, DB). I'm not sure which course covers it best.
Thank you for your patience.
Michael
Hi Micheal,
The problem you are currently having where you have to refresh twice to get the PivotTables to update is caused by loading the data to a table in Excel and then loading it to the Data Model/PowerPivot.
See the Power Query course lesson 5.02 for the correct procedure for loading data to the data model/Power Pivot.
Mynda
Hi Mynda
Yes, that helped. I removed the table in the dashboard workbook and reworked the power query in the Data workbook to feed the data model. Now my pivot charts are pulling directly from the model data in PP.
Your time and help are much appreciated!
Michael
