Create a dynamic chart with code

[carloshernandezy]

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"
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Sheets("TOC").Range("C2:C3065"),
PlotBy:= _
xlColumns
ActiveChart.Location Where:=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

 

[Guest]

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 Where:=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