Graphics with moving source data

[rafaelspeixoto]

Hello everyboby.

I need a little help once again with macro writing.

Every month I have to build a powerpoint presentation with charts containing data of the whole year up to last month's data. In other words, I have to update the source data of each chart (adding one more column corresponding to the following month).
Is there a way for me to just choose the last month (maybe from a cell with validation) and make each chart source data change based on that? The only way I have figured out would be to write one chart range for each possible month for each chart, but it is obviously not very efficient...

Thanks a lot in advance

 

[Datsmart]

Take a look at this link.
http://peltiertech.com/Excel/Charts/Dynamics.html

Lots of good suggestions.

 

[Datsmart]

Another way I have used is to Refresh a named range and assign it to the your chart like this:

Sub NewChartRange()
'Assign Range Name to Range
    ActiveWorkbook.Names.Add Name:="MyChtRange", RefersTo:=Range("A11").CurrentRegion
'Assign Range Name to Chart
    Set CurrentChart = Sheets("Criteria").ChartObjects(1).Chart
    CurrentChart.SetSourceData Source:=Sheets("Criteria").Range("MyChtRange"), PlotBy:=xlColumns
End Sub

 

[tusharm]

The ideal would be if you inserted all the charts in PP as linked objects. Then, in addition to the link you have already received, you could use the ideas at
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html
to create a drop-down driven solution that updates all the charts in Excel.

Now, when you open the presentation in PowerPoint, simply update all the links.

Hello everyboby.

I need a little help once again with macro writing.

Every month I have to build a powerpoint presentation with charts containing data of the whole year up to last month's data. In other words, I have to update the source data of each chart (adding one more column corresponding to the following month).
Is there a way for me to just choose the last month (maybe from a cell with validation) and make each chart source data change based on that? The only way I have figured out would be to write one chart range for each possible month for each chart, but it is obviously not very efficient...

Thanks a lot in advance

 

[rafaelspeixoto]

Thanks a lot for the answer! I found some really nice ways to do the job in the link to Peltier' site you sent (and some of his links too...).
I placed a dropdown box to allow the user to select the months that should be available in the chart using defined Names with Offset function.
It works very well and allows me to get back to old graphics quicky in case someone asks me one of those nice questions like "Do you still have that chart from April?"

By the way, tusharm, The link you sent was the link I found in Peltier's page that gave me the idea for the dropdown box... Wish I had seen your answer later (forgot to ask to be notified in of the replies)