Excel 2007: Graph macro using reletive reference?

[Abel MacAdam]

Hi,

I recorded the following macro:
Sub Past_month_click()
'
ActiveSheet.ChartObjects("Graph 15").Activate
ActiveSheet.ChartObjects("Graph 15").Activate
ActiveChart.SetSourceData Source:=Range("B50:H54")
End Sub

What do I want to accomplish? I have a range of data per month. When I do my
thing in the sheet I want to add the last month to my graph. In Excel I have
to right click the graph, change the data range, press the right key,
followed by pressing the Enter key. This I would like to do by the macro. So,
the Range("B50:H54") must become a relative reference. What do I need to
change in the above code to accomplish that?

Abel

 

[Joel]

Something like this


Sub Past_month_click()
LastRow = 15
Set MyRange = Range("B1:B" & LastRow)
'
ActiveSheet.ChartObjects("Graph 15").Activate
ActiveChart.SetSourceData Source:=MyRange
End Sub

 

[Abel MacAdam]

Hi Joel,

Thanks for helping me. Sadly, no. The graph is wiped clean. The range it
uses for its data is altered to a location containing no data. Even a 'Undo'
did not get me my data back.

Abel

 

[Abel MacAdam]

I have been thinking. I have the following table (points are used to get
everything lined out nicely):

..............Jan.......Feb.......Mrt.......Apr.......Mei.......Jun.......Jul
Totaal....127.......148.......152.......153.......129.......100.......112
Spoed......22........26........37........27........14........13........14
Normaal..100.......109.......109.......118.......109........80........90
Changes....5........13.........6.........8.........6.........7.........8

The graph contains data upto and including the Jun column. The macro needs
to expand the data range with the Jul column.

I recorded a macro using relative references, executing the following steps:
1. Right click the graph, choose 'Select datarange...' (need to translate, I
use the Dutch version);
2. Choose the Datarange of Graph (above text box in the window)
3. Go to the field containing 'Changes' (bottom left of my table);
4. Press Shift and End together, followed by pressing the right key (the row
from changes to the cell off the Jul column is now selected);
5. Press Shift End and Up, followed by an Up (the whole table as depicted
above is selected)

I got the following code:
Sub SelectDataRange()
'
' SelectDataRange
'
ActiveSheet.ChartObjects("Graph 15").Activate
ActiveChart.SetSourceData Source:=ActiveCell.Range("A1:G5")
End Sub

Will this macro be usable in August, September, and so on? And if not, what
do I need to use to get what I described in my five steps? Will a named cell
(cell containing 'Changes' gets the name 'Anchor') be usable?

Abel

 

[Joel]

I just gave an example of how to change the code. I didn't know how your
range was changing dynamically. You had a range of B50:H54 and my code only
had B1:B15.

The chart object on still on the worksheet. It didn't get deleted. There
is just no data to fill the chart so it became invisible.