Moving Charts in a Macro

[gage25]

I am needing some help. I have made a macro that will build a chart, but I want to be able to move the chart to a specific location in the sheet where the graph will be. I go through the steps while recording the macro, but when I get ready to run the macro, I run into an error, everytime! What is Excel doing that is screwing this up? Is there a way to modify the macro VBA to achieve this?

 

[Andrew Poulsom]

This code creates a chart and anchors it to cell D2:

    Charts.Add
    With ActiveChart
'       *** Change sheet and range references to suit ***
        .SetSourceData Source:=Sheets("Sheet1").Range("A1:B5")
'       *** Change type to suit ***
        .ChartType = xlColumnClustered
'       *** Change sheet name to suit ***
        .Location Where:=xlLocationAsObject, Name:="Sheet1"
'       *** Change top left cell location to suit ***
    End With
'   Location method creates new object so start again
    With ActiveChart
'       *** Change top left cell location to suit ***
        .Parent.Top = Range("D2").Top
        .Parent.Left = Range("D2").Left
    End With
End Sub

 

[Iridium]

Another way though Andrew's is probably better as you can ock it to a cell rather than playing around with the width/left values

Application.Worksheets("Sheet").Activate
Dim rng As Range
Set rng = Application.Names("datarange").RefersToRange

Dim cht As ChartObject
Set cht = ActiveSheet.ChartObjects.Add _
(Left:=5, Width:=1000, Top:=1490, Height:=750)
With cht
.Chart.ChartType = xlXYScatter
.Chart.SetSourceData rng, xlColumns
.Chart.Location Where:=xlLocationAsObject, Name:="Sheet"
.Chart.HasTitle = True
.Chart.ChartTitle.Text = "='Sheet'!R79C1"
.Chart.Axes(xlCategory, xlPrimary).HasTitle = True
.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Actuation no."
.Chart.Axes(xlValue, xlPrimary).HasTitle = True
.Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Title"
End With
etc. etc.

 

[gage25]

Thanks for the help. That solved my problem!

Gracias