Graphics with moving source data

rafaelspeixoto

Board Regular
Joined
May 14, 2007
Messages
85
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Another way I have used is to Refresh a named range and assign it to the your chart like this:
Code:
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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

Board Regular
Joined
May 14, 2007
Messages
85
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) :p
 

Forum statistics

Threads
1,181,410
Messages
5,929,774
Members
436,688
Latest member
sunnyBNH013

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top