First, for others, this has been cross-posted at ExcelForum.com.
MylesMc, have you cross posted elsewhere too? If so, could you say where please?
Second, the problem:
Try the following in your sample workbook:
Code:
Sub blah()
Range("A1").Select 'in case the chart is selected.
Range("A1").Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
With ActiveSheet.ChartObjects("Chart 1").Chart
'delete all existing series first:
For i = 1 To .SeriesCollection.Count
.SeriesCollection(1).Delete
Next i
For Each ar In Intersect(ActiveSheet.UsedRange, Columns("B:B")).Offset(1).SpecialCells(xlCellTypeConstants, 23).Areas
' ar.Offset(, -1).Resize(, 3).Select
' ar.Cells(1).Offset(, -1).Select
' ar.Columns(1).Select
' ar.Columns(2).Select
With .SeriesCollection.NewSeries
.XValues = ar.Columns(1)
.Values = ar.Columns(2)
.Name = ar.Cells(1).Offset(, -1)
With .Border
.Weight = xlThin
.LineStyle = xlAutomatic
.ColorIndex = 1
End With
.MarkerStyle = xlNone
.Smooth = True
End With
Next ar
End With
Range("A1").RemoveSubtotal
End Sub
This was developed in Excel 2003.
Bookmarks