Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically selecting active chart | Excel Programming | |||
Selecting Worksheets from active to end | Excel Programming | |||
Selecting cells on active row | Excel Discussion (Misc queries) | |||
Selecting an active cell | Excel Programming | |||
Selecting the active row | Excel Programming |