Prev: Text running
Next: Macro command not running in excel 2007
From: Curt on 15 Apr 2010 13:23
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
From: Wouter HM on 15 Apr 2010 14:59
Hi Curt,

There is only one line of code needed:

ActiveSheet.ChartObjects.Item(1).Activate

HTH,

Wouter
From: Curt on 16 Apr 2010 09:06
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
> .
>
From: Curt on 16 Apr 2010 10:04
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
> > .
> >
 | 
Pages: 1
Prev: Text running
Next: Macro command not running in excel 2007