Automatically selecting active chart
 

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

On 15/04/2010 18:23, Curt wrote:
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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info