I am trying to make automate the creation of a graph that I have to create quite often and on many pages. I have not used Visual basic much but have taken a class on programing in qbasic so I do understand most of the more basic concepts but don't understand the more advanced stuff.

The Columns I use are always the same.
The only two things that I will change are what spreadsheet it the data is on and the upper and lower limits. The problem is that I can not seem to put any kind of a variable into the appropriate fields.

Here is the relevant code
Charts.Add
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = "=('30030'!R2C2:R10000C2,'30030'!R2C6:R10000C6 )"
    ActiveChart.SeriesCollection(1).Values = "=('30030'!R2C3:R10000C3,'30030'!R2C7:R10000C7 )"
    ActiveChart.SeriesCollection(1).Name = "=""Series"""
    ActiveChart.SeriesCollection(2).XValues = "={0,600}"
    ActiveChart.SeriesCollection(2).Values = "={2,2}"
    ActiveChart.SeriesCollection(2).Name = "=""Upper limit"""
    ActiveChart.SeriesCollection(3).XValues = "={0,600}"
    ActiveChart.SeriesCollection(3).Values = "={0,0}"
    ActiveChart.SeriesCollection(3).Name = "=""Lower limit"""
    ActiveChart.Location where:=xlLocationAsObject, Name:="30030"

The Name of the sheet is 30030

The other question is how do you move a graph to a location in a spreadsheet. I have seen a few bits of code that supposedly do this but none have worked.

I am using Microsoft Excel 2003 SP2 and Microsoft Visual Basic 6.3 if it makes any difference.

Thanks in advance for any help.

Try the following VBA Code in a procedure. It will position and size your chart, get the values for your chart, set the scale for the y variable, format the gridlines and plot area.


    Dim ws As Worksheet

    Set ws = Sheets("SHIPMENTS")
    ws.ChartObjects.Delete

    Dim co As ChartObject
    Set co = ws.ChartObjects.Add(1, 1, 1, 1)

    'Position and size the chart

    Dim RngToCover As Range
    Set RngToCover = ws.Range(ws.Cells(20, 1), ws.Cells(20 + 20, 9))
    co.Height = RngToCover.Height
    co.Width = RngToCover.Width
    co.Top = RngToCover.Top
    co.Left = RngToCover.Left

    'Chart type line
    co.Chart.ChartType = xlLine


    With co.Chart.SeriesCollection.NewSeries 'Series 1
        .Name = "Shipments"
        .Values = ws.Range("SHIPMENTS") ' Range name on worksheet with Y variable
        .XValues = ws.Range("DATE") ' Range name on worksheet with X variable
    End With

    'Set chart Title, and Y variable Scale


    With co.Chart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Shipments"
        .ChartTitle.Characters.Font.Size = 20
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Dollars"
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Font.Size = 12
        With .Axes(xlValue)
            .MinimumScale = 0
            .MaximumScale = 100000
            .MinorUnit = 1000
            .MajorUnit = 10000
            .MajorTickMark = xlCross
            .MinorTickMark = xlInside
            .TickLabelPosition = xlNextToAxis
        End With
    End With

    'Format gridline and plotArea

    co.Activate

    With co.Chart.Axes(xlValue).MajorGridlines.Border
        .ColorIndex = 57
        .Weight = xlHairline
        .LineStyle = xlDot
    End With

    co.Chart.PlotArea.Interior.ColorIndex = xlNone

If you chart data ranges change, consider making them dynamic ranges.