Move a chart in VBA

[Lucilla]

Hello guys,
I have a big problem. I am writing a macro to create a graph from a table. then I want that this graph is moved to another sheet in the same workbook, but excel crashs each time and then it recovers the file without the last changes I have done
I do't know how to do it, because the same instruction in another macro worked...
this is the code of the function:

Function AddTrend(KPI, EqType, StartEq, EndEq)
    Range("A1").Select
    Month_Range = Range(Cells(StartEq, 5), Cells(EndEq, 5)).Value
    PT_Name = Cells(2, 6).Value
    PT_Range = Range(Cells(StartEq, 6), Cells(EndEq, 6)).Value
    TGT_Name = Cells(1, 11).Value
               KPI_Column = 7
            TGT_Column = 11
            CaptionAxis = "[%]"
            ActiveSheet.Shapes.AddChart.Select
            ActiveChart.ApplyChartTemplate ( _
                "TREND-.crtx")
        '    ActiveChart.Name = KPI
    KPI_NAME = Cells(2, KPI_Column)
    KPI_Range = Range(Cells(StartEq, KPI_Column), Cells(EndEq, KPI_Column)).Value
 
    ActiveChart.SeriesCollection(1).Name = KPI_NAME     ActiveChart.SeriesCollection(1).Values = KPI_Range
    ActiveChart.SeriesCollection(2).Name = PT_Name     ActiveChart.SeriesCollection(2).Values = PT_Range
    ActiveChart.SeriesCollection(1).XValues = Month_Range    With ActiveChart.ChartTitle
        .Caption = KPI
    End With
    With ActiveChart.Axes(xlValue, xlSecondary)
        .HasTitle = True
        .MaximumScaleIsAuto = True
        .MinimumScaleIsAuto = True
        With .AxisTitle
            .Caption = "Production Time [h]"
        End With
    End With
    With ActiveChart.Axes(xlValue, xlPrimary)
        .HasTitle = True
        .MaximumScaleIsAuto = True
        .MinimumScaleIsAuto = True
        With .AxisTitle
            .Caption = CaptionAxis
        End With
    End With
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Graph - "
 
    ActiveChart.Location Where:=StartEq
End Function

it works until

 ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="3 "

then it craschs.

someone can help me!?!? please!!!
just a note: TREND-.crtx is a template I created.

 

[Voorblad]

Hi

I just encountered the same problem with my excel.
I figured out that as soon as you leave out a name for the new chartsheet, this code works fine, so try the same code just without the name ext

ActiveChart.Location Where:=xlLocationAsNewSheet

 

[RoryA]

Simpler to use:

activeworkbook.charts.add

to add a new chart sheet in the first place.

 

[Voorblad]

Thanks

Will definitely use that instead in the future.

Seeing that you've just posted I'm hoping if you know the code to change the seriescollection of a chart's outline color to black

I tried this code:

activechart.seriescollection(1).format.line.forecolor.rgb = rgb(0,0,0)

It doesnt throw an error but also doesnt change the color of the series's outline? Any suggestions how to change the color

 

[RoryA]

That works for me.

 

[Voorblad]

It doenst work for me, but just tried

ActiveChart.SeriesCollection(1).Border.ColorIndex = 1

and that seemed to do the trick, thanks anyway

 

[RoryA]

What kind of chart?

 

[Lucilla]

Ehi guys, Thanks for help me! I was waiting for a solution since October!!!

Just to better understand: I should use

activeworkbook.charts.add

instead of

ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="3 "

, right?!

 

[RoryA]

No - instead of:

ActiveSheet.Shapes.AddChart.Select

 

[Lucilla]

ok, thanks! I will test the solution with the entire code and I will let you know!

Thanks a lot!!!