I have a workbook that produces a PowerPoint slide deck with statistics for 4 different products for 150 cities. Well, it's supposed to produce the slide deck. My problem is the charts are not updating as the macro runs.
The 150 city names is cycled through using a macro to update a the city filter of a pivot table (and slicers on each product chart tab). Each product chart uses the city name to update their respective statistics using SUMIFS statements. As the macro runs and cycles through each product tab, I can see the data updating in the tables for the chart, but my charts are not updating to reflect the data. As the macro cycles through each city, the charts are exported to my PowerPoint.
The whole process is controlled by the macro as selecting each city manually and then exporting to PowerPoint would be too time consuming. And having a workbook with 150 tabs with 4 charts per tab is not an option I'm considering.
I think I have tried every option possible as discussed on your website and others with no luck. I'm hoping you can help me.
Simplified code below to cycle through the names where the wait would be the link to the export macro.
Thank you
Sub Macro1()
Application.Calculation = xlCalculationAutomatic
LastRow = Sheets("PLANTS").Cells(1000, 1).End(xlUp).Row
For Plt = 2 To LastRow
Sheets("SPM Data").Select
Plant = Sheets("Plants").Cells(Plt, 3)
Range("V1").Select
ActiveSheet.PivotTables("PlantSelect").PivotFields("PLANT").CurrentPage = Plant
Sheets("SPM Data").PivotTables("PlantSelect").Update
Sheets("SPFC").Select
DoEvents
Application.Wait (Now + TimeValue("00:00:02"))
Next Plt
End Sub
The 150 city names is cycled through using a macro to update a the city filter of a pivot table (and slicers on each product chart tab). Each product chart uses the city name to update their respective statistics using SUMIFS statements. As the macro runs and cycles through each product tab, I can see the data updating in the tables for the chart, but my charts are not updating to reflect the data. As the macro cycles through each city, the charts are exported to my PowerPoint.
The whole process is controlled by the macro as selecting each city manually and then exporting to PowerPoint would be too time consuming. And having a workbook with 150 tabs with 4 charts per tab is not an option I'm considering.
I think I have tried every option possible as discussed on your website and others with no luck. I'm hoping you can help me.
Simplified code below to cycle through the names where the wait would be the link to the export macro.
Thank you
Sub Macro1()
Application.Calculation = xlCalculationAutomatic
LastRow = Sheets("PLANTS").Cells(1000, 1).End(xlUp).Row
For Plt = 2 To LastRow
Sheets("SPM Data").Select
Plant = Sheets("Plants").Cells(Plt, 3)
Range("V1").Select
ActiveSheet.PivotTables("PlantSelect").PivotFields("PLANT").CurrentPage = Plant
Sheets("SPM Data").PivotTables("PlantSelect").Update
Sheets("SPFC").Select
DoEvents
Application.Wait (Now + TimeValue("00:00:02"))
Next Plt
End Sub
