Create a dynamic chart with code
 

Hello,

I am trying to create a dynamic chart using code in a diferent workbook
from the code one and it´s imposible for me. I can create one dynamic
chart in the same workbook of the code but when I try this code to
create the grph in a different workbook the problems appears.

Thank you in advance.
The data is coloumn "C".
The code that generate error is the one with

___The code is this one ____

Sub Grafico()
'

ActiveWorkbook.Names.Add Name:="RngC", RefersToR1C1:= _
"=OFFSET(Hoja1!R2C3,0,0,COUNTA(Hoja1!C3)-1)"


Sheets(1).Name = "TOC"

Windows("TOC").Activate
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Sheets("TOC").Range("C2:C3065"),
PlotBy:= _
xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="TOC"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "TOC en "
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "ppb"
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowNone,
LegendKey:=False
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
End Sub

Create a dynamic chart with code
 

The following code assumes that the target workbook has already been opened
and is the active workbook. Your code appears to imply this.

Sub Grafico()
Dim wb As Workbook
Dim ws As Worksheet
Dim cht As Excel.Chart

Set wb = ActiveWorkbook
wb.Names.Add Name:="RngC", RefersToR1C1:= _
"=OFFSET(Hoja1!R2C3,0,0,COUNTA(Hoja1!C3)-1)"
Application.ScreenUpdating = False
Set ws = wb.Sheets(1)
ws.Name = "TOC"
Set cht = ws.ChartObjects.Add(50, 50, 400, 300).Chart
With cht
.ChartType = xlXYScatterSmoothNoMarkers
.SetSourceData Source:=ws.Range("C2:C3065"), PlotBy:=xlColumns
.Location Whe=xlLocationAsObject, Name:="TOC"
.HasTitle = True
.ChartTitle.Characters.Text = "TOC en "
With .Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With .Axes(xlValue)
.HasTitle = True
.AxisTitle.Characters.Text = "ppb"
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
.HasLegend = False
With .PlotArea
With .Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With .Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
End With
End With
Application.ScreenUpdating = True
End Sub

Regards,
Greg