Thread: Creating more than one graph (chart) on a worksheet

Hi,

I have a spreadsheet of store sales, costs and average sales. The data for each store is contained within a single tab, so I have 3 tabs for 3 stores.

I need to be able to create two graphs on each tab. The first graph containing sales and average sales, and the second graph containing costs and average sales.

I recorded a macro, which works fine for creating the first graph. But when I copy the code and add it too the end (and change the series to the cost data), I get errors.

I would also like to be able to position these graphs. Graph 1 - cells A34 to F50 and Graph 2 - cells H34 - O50.

Of course I will need to rerun this code for the remaining stores as well.

Hi Teylyn,

Will do so in a few hours. My security would not allow me to upload the file.

I have managed to fix the problem of creating two graphs on same worksheet. But they open on top of each other, not side by side.

Cheers

Here is the spreadsheet of sample data, and the code I am using:

I need to have graph 1 start at A34 through to F50, and graph 2 start H34 through to O50.

I also need to repeat for the remaining two tabs (in reality I have 60 tabs, so really need this to be automated)

Sub CreateMulitpleCharts()

'Sales chart
    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Sheets("Store260").Range("A9")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = "=Store260!R2C2:R2C13"
    ActiveChart.SeriesCollection(1).Values = "=Store260!R3C2:R3C13"
    ActiveChart.SeriesCollection(1).Name = "=Store260!R3C1"
    ActiveChart.SeriesCollection(2).XValues = "=Store260!R2C2:R2C13"
    ActiveChart.SeriesCollection(2).Values = "=Store260!R6C2:R6C13"
    ActiveChart.SeriesCollection(2).Name = "=Store260!R6C1"
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Store260"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Store 260 Sales"
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
    ActiveChart.PlotArea.Select
    With Selection.Border
        .Weight = xlThin
        .LineStyle = xlNone
    End With
    With Selection.Interior
        .ColorIndex = 2
        .PatternColorIndex = 1
        .Pattern = xlSolid
    End With
    
 'Costs chart
    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Sheets("Store260").Range("A9")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = "=Store260!R2C2:R2C13"
    ActiveChart.SeriesCollection(1).Values = "=Store260!R4C2:R4C13"
    ActiveChart.SeriesCollection(1).Name = "=Store260!R4C1"
    ActiveChart.SeriesCollection(2).XValues = "=Store260!R2C2:R2C13"
    ActiveChart.SeriesCollection(2).Values = "=Store260!R6C2:R6C13"
    ActiveChart.SeriesCollection(2).Name = "=Store260!R6C1"
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Store260"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Store 260 Costs"
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
    ActiveChart.PlotArea.Select
    With Selection.Border
        .Weight = xlThin
        .LineStyle = xlNone
    End With
    With Selection.Interior
        .ColorIndex = 2
        .PatternColorIndex = 1
        .Pattern = xlSolid
    End With
    
End Sub