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 : pray2:

[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/

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 ******************************************

and


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

[mercmannick]

:beerchug: 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

:beerchug::beerchug::beerchug::beerchug::beerchug::beerchug::beerchug::beer chug:

Merc