Solved: little help with add.charts

[mercmannick]

Sub Add_Chart()

    
    Charts.Add
    ActiveChart.SetSourceData Source:=Sheets("Pivot S70").Range("A14")
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveChart.PlotArea.Select
    With Selection.Border
        .ColorIndex = 16
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    Selection.Fill.TwoColorGradient Style:=msoGradientHorizontal, Variant:=3
    With Selection
        .Fill.Visible = True
        .Fill.ForeColor.SchemeColor = 40
        .Fill.BackColor.SchemeColor = 2
    End With
    ActiveChart.Legend.Select
    Selection.Delete
    ActiveChart.SeriesCollection(1).Select
    With ActiveChart.ChartGroups(1)
        .Overlap = 100
        .GapWidth = 50
        .HasSeriesLines = False
        .VaryByCategories = False
    End With
    ActiveChart.Axes(xlCategory).Select
    Selection.TickLabels.AutoScaleFont = True
    With Selection.TickLabels.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    ActiveChart.Axes(xlValue).Select
    Selection.TickLabels.AutoScaleFont = True
    With Selection.TickLabels.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    ActiveChart.ChartTitle.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 16
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    Selection.Characters.Text = "S70 & S40 Shortage's (Part No's)"
    ActiveChart.ChartArea.Select
End Sub

First time i have delved into adding charts VBA

is there any way of reducing this code , as there seems to be an awfull lot of redundant data , (this is from Macro Recorder)


Many Thanks

Merc

[mercmannick]

also is there a way of putting todays date into the chart title as well

Thanks Merc

[mdmackillop]

Hi Merc
Here's the first part. I'll leave the rest to you. If you don't have it yet, get SmartIndenter, invaluable with convoluted code like this
http://www.bmsltd.ie/Indenter/

[vba]Sub Add_Chart2()
Charts.Add
With ActiveChart
.SetSourceData Source:=Sheets("Sheet1").Range("A14:A26")
.Location Where:=xlLocationAsNewSheet
With .PlotArea
.Border.ColorIndex = 16
.Border.Weight = xlThin
.Border.LineStyle = xlContinuous
.Fill.TwoColorGradient Style:=msoGradientHorizontal, Variant:=3
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 40
.Fill.BackColor.SchemeColor = 2
End With
.Legend.Delete
'ActiveChart.SeriesCollection(1).Select
With .ChartGroups(1)
.Overlap = 100
.GapWidth = 50
.HasSeriesLines = False
.VaryByCategories = False
End With
With .Axes(xlCategory).TickLabels
.AutoScaleFont = True
With .Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
End With
End With
End With
'trimmed to here ******************************************[/vba]

and

[vba]
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "S70 & S40 Shortage's (Part No's) " & Format(Now(), "d mmmm yyyy")
End With

[/vba]

[mercmannick]

brilliant cheers m8

only query dosent matter if not possible , on pivot chart what this creates
on chart sheet , is there a way of not having page fields and series fields boxes showing ?

Many thanks

Merc

[mercmannick]

Sussed it many thanks all



Merc