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