Bruce,

Sub ButtonAll()
Dim myRange As Range
Set myRange = ActiveCell
'or use
'Set myRange = ActiveSheet.Cells(CInt(InputBox("Enter row Number")), 3)

ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R" & _
myRange.Row & "C3:R" & myRange.Row & "C9"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R" & _
myRange.Row & "C2"
End Sub

HTH,
Bernie
MS Excel MVP


"Bruce Neylon" wrote in message
link.net...
I was given a spreadsheet with the following columns:
a button column (to run a macro)
a persons name
6 monthly columns of figures

When the button is clicked the macro associated is run and a cute little chart for the row pops
up. The first 3 rows have the buttons and the associated macros. My job, is to populate the
macro for the rest of the 1780 rows.
The code for the current Macros :
Sub Button1()
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R3C3:R3C9"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R3C2"
End Sub
Sub Button2()
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R4C3:R4C9"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R4C2"
End Sub
Sub Button3()
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R5C3:R5C9"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R5C2"
End Sub

Maybe it's because I am a programer, but I have to believe there is an easier way of doing this.
One sub that will, based upon the row of the button clicked, run for every row and create the
chart.
I have been searching microsoft and the rest of the web with no luck. Might be able to find
something if I had a good grasp of the terminology.

Thank You,
Bruce