Automatically selecting active chart

[Curt]

I have the following marco below. I would like to change it so that I do not
have to select an active chart before running it. Instead, I would like it
to always run for the chart in sheet titled "Benchmark Comp Chart". Please
note, there is never more than one chart in that sheet.

Currently, since I have to select an active chart, I have to run this while
my active sheet is "Benchmark Comp Chart". Going forward, I wish to run it
while my active sheet is titled "Misc.".

Sub GetChartValues()
Dim NumberOfRows As Integer
Dim X As Object
Counter = 2

' Calculate the number of rows of data.
NumberOfRows = UBound(ActiveChart.SeriesCollection(1).Values)

Worksheets("Benchmark Comp Chart").Cells(1, 1) = "X Values"

' Write x-axis values to worksheet.
With Worksheets("Benchmark Comp Chart")
.Range(.Cells(2, 1), _
.Cells(NumberOfRows + 1, 1)) = _
Application.Transpose(ActiveChart.SeriesCollection(1).XValues)
End With

' Loop through all series in the chart and write their values to
' the worksheet.
For Each X In ActiveChart.SeriesCollection
Worksheets("Benchmark Comp Chart").Cells(1, Counter) = X.Name

With Worksheets("Benchmark Comp Chart")
.Range(.Cells(2, Counter), _
.Cells(NumberOfRows + 1, Counter)) = _
Application.Transpose(X.Values)
End With

Counter = Counter + 1
Next

End Sub

 

[Wouter HM]

Hi Curt,

There is only one line of code needed:

ActiveSheet.ChartObjects.Item(1).Activate

HTH,

Wouter

 

[Curt]

Thank you, that worked, but I wish to run this macro from another sheet.

I still have to run this while my active sheet is "Benchmark Comp Chart". I
wish to run it while my active sheet is titled "Misc.".

thanks,

 

[Andy Pope]

Hi,

Set a reference to the chart and use that instead of activechart.
In the code I set a reference to the activechart.

Sub GetChartValues()
Dim NumberOfRows As Integer
Dim X As Object
Dim chtTemp As Chart

Set chtTemp = ActiveChart ' or a reference to the required chart
''' Set chtTemp = ActiveSheet.ChartObjects(1).Chart

Counter = 2

' Calculate the number of rows of data.
NumberOfRows = UBound(chtTemp.SeriesCollection(1).Values)

Worksheets("Benchmark Comp Chart").Cells(1, 1) = "X Values"

' Write x-axis values to worksheet.
With Worksheets("Benchmark Comp Chart")
.Range(.Cells(2, 1), _
.Cells(NumberOfRows + 1, 1)) = _
Application.Transpose(chtTemp.SeriesCollection(1).XValues)
End With

' Loop through all series in the chart and write their values to
' the worksheet.
For Each X In chtTemp.SeriesCollection
Worksheets("Benchmark Comp Chart").Cells(1, Counter) = X.Name

With Worksheets("Benchmark Comp Chart")
.Range(.Cells(2, Counter), _
.Cells(NumberOfRows + 1, Counter)) = _
Application.Transpose(X.Values)
End With

Counter = Counter + 1
Next

End Sub


Cheers
Andy

 

[Curt]

I just recorded a macro that did the rest. I am all set now. Thanks