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

Hi Curt,

There is only one line of code needed:

ActiveSheet.ChartObjects.Item(1).Activate

HTH,

Wouter

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,

"Wouter HM" wrote:

> Hi Curt,
>
> There is only one line of code needed:
>
> ActiveSheet.ChartObjects.Item(1).Activate
>
> HTH,
>
> Wouter
> .
>

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

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

"Curt" wrote:

> 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,
>
> "Wouter HM" wrote:
>
> > Hi Curt,
> >
> > There is only one line of code needed:
> >
> > ActiveSheet.ChartObjects.Item(1).Activate
> >
> > HTH,
> >
> > Wouter
> > .
> >