Excel 2007: Graph macro using reletive reference?
 

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

Excel 2007: Graph macro using reletive reference?
 

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" wrote:

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

Excel 2007: Graph macro using reletive reference?
 

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

"Joel" wrote:

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" wrote:

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

Excel 2007: Graph macro using reletive reference?
 

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.......1 09........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

"Abel MacAdam" wrote:

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

"Joel" wrote:

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" wrote:

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

Excel 2007: Graph macro using reletive reference?
 

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.

"Abel MacAdam" wrote:

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

"Joel" wrote:

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" wrote:

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