Adding charts to multiple worksheets automatically

**qualitygeek** · 11-08-2009, 09:03 AM

I am trying to automatically add charts (4) to mulitple worksheets (39) and do not know how to accomplish. I have recorded a macro during the initial chart creation in the "blank" worksheet but it returns a "compile error". Sample attached with three worksheets, data and macro. All help would be appreciated.

**mrice** · 11-08-2009, 04:49 PM

I've modified your recorded code to create the first of your graphs on each sheet. Hopefully you will be able to use this example to adapt the remainder.

Code:

Sub Autochart()

For Each Sheet In ThisWorkbook.Sheets
    If ActiveWindow.Visible = True Then
        Sheet.Activate
        Range("T3:V33").Select
        Charts.Add
        ActiveChart.ChartType = xlLineMarkers
        ActiveChart.Location Where:=xlLocationAsObject, Name:=Sheet.Name
        ActiveChart.SetSourceData Source:=ActiveSheet.Range("T3:V33"), PlotBy:= _
            xlColumns
        
        With ActiveChart
            .HasTitle = True
            .ChartTitle.Characters.Text = "SPC Data Dimension (Average)"
            .Axes(xlCategory, xlPrimary).HasTitle = False
            .Axes(xlValue, xlPrimary).HasTitle = False
        End With
        With Selection.Interior
            .ColorIndex = 35
            .Pattern = 1
        End With
        ActiveChart.ChartArea.Left = 1300
        ActiveChart.ChartArea.Top = 600
        ActiveChart.ChartArea.Width = 450
        ActiveChart.ChartArea.Height = 300
        ActiveChart.SeriesCollection(2).Select
        With Selection.Border
            .ColorIndex = 1
            .Weight = xlMedium
            .LineStyle = xlDash
        End With
        With Selection
            .MarkerBackgroundColorIndex = xlAutomatic
            .MarkerForegroundColorIndex = xlAutomatic
            .MarkerStyle = xlNone
            .Smooth = False
            .MarkerSize = 7
            .Shadow = False
        End With
        ActiveChart.Legend.Select
        Selection.Position = xlBottom
        ActiveChart.SeriesCollection(3).Select
        With Selection.Border
            .ColorIndex = 1
            .Weight = xlMedium
            .LineStyle = xlDash
        End With
        With Selection
            .MarkerBackgroundColorIndex = xlAutomatic
            .MarkerForegroundColorIndex = xlAutomatic
            .MarkerStyle = xlNone
            .Smooth = False
            .MarkerSize = 7
            .Shadow = False
        End With
    End If
Next Sheet
End Sub

**qualitygeek** · 11-09-2009, 02:40 PM

A run time error 91 (Object variable or with block variable not set) occurs at line "With Selection.Interior". I tried to comment this out to see if it would run beyond this line, but it does not. Any thoughts or suggestions?

**mrice** · 11-10-2009, 01:01 PM

Try commenting this and the following three lines as an experiment.

**qualitygeek** · 11-10-2009, 09:16 PM

I commented out the four lines and then got a compile error on [ActiveChart.SetSourceData Source:=Sheets("Blank").Range("X3:Y33"), PlotBy:=] line. I'm afraid I don't know enough about the coding to understand too much of what is, or is not going on.
Any recommendations on learning materials?

Thank you for your efforts.
P

**mrice** · 11-11-2009, 04:00 PM

You've got an extra carriage return in there. The line needs to be all on one line.

Code:

    ActiveChart.SetSourceData Source:=Sheets("Blank").Range("X3:Y33"), PlotBy:=xlColumns

I would suggest that after making the above change, paste my code into a new module and change the name of the sub so that we don't have two with the same name (e.g. change to Autochart1).

Code:

Sub Autochart1()

For Each Sheet In ThisWorkbook.Sheets
    If ActiveWindow.Visible = True Then
        Sheet.Activate

'repeat this section with the appropriate change in ranges and parameters
        Range("T3:V33").Select  'Change here
        Charts.Add
        ActiveChart.ChartType = xlLineMarkers
        ActiveChart.Location Where:=xlLocationAsObject, Name:=Sheet.Name
        ActiveChart.SetSourceData Source:=ActiveSheet.Range("T3:V33"), PlotBy:= xlColumns  'Change here

        
        With ActiveChart
            .HasTitle = True
            .ChartTitle.Characters.Text = "SPC Data Dimension (Average)"  'Change here

            .Axes(xlCategory, xlPrimary).HasTitle = False
            .Axes(xlValue, xlPrimary).HasTitle = False
        End With
        With Selection.Interior
            .ColorIndex = 35
            .Pattern = 1
        End With
        ActiveChart.ChartArea.Left = 1300  'Change here
        ActiveChart.ChartArea.Top = 600 'Change here
        ActiveChart.ChartArea.Width = 450 'Change here
        ActiveChart.ChartArea.Height = 300 'Change here
        ActiveChart.SeriesCollection(2).Select
        With Selection.Border
            .ColorIndex = 1
            .Weight = xlMedium
            .LineStyle = xlDash
        End With
        With Selection
            .MarkerBackgroundColorIndex = xlAutomatic
            .MarkerForegroundColorIndex = xlAutomatic
            .MarkerStyle = xlNone
            .Smooth = False
            .MarkerSize = 7
            .Shadow = False
        End With
        ActiveChart.Legend.Select
        Selection.Position = xlBottom
        ActiveChart.SeriesCollection(3).Select
        With Selection.Border
            .ColorIndex = 1
            .Weight = xlMedium
            .LineStyle = xlDash
        End With
        With Selection
            .MarkerBackgroundColorIndex = xlAutomatic
            .MarkerForegroundColorIndex = xlAutomatic
            .MarkerStyle = xlNone
            .Smooth = False
            .MarkerSize = 7
            .Shadow = False
        End With
    End If
'End of repeat section

Next Sheet
End Sub