Hey Guys,
The code below represents a function to autoplot data in the attached spreadsheet. However, it only works if I have 1 blank chart inserted into the spreadsheet. What I would like is to have this code just create a new chart, despite if there are other charts already on the sheet (and just leave them alone).
The AutoPlot code I have is the following:
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 = xlAutomatic
End With
.MarkerStyle = xlNone
.Smooth = True
End With
Next ar
End With
Range("A1").RemoveSubtotal
End Sub
Any and all help is appreciated immensely,
Myles Mc
The code below represents a function to autoplot data in the attached spreadsheet. However, it only works if I have 1 blank chart inserted into the spreadsheet. What I would like is to have this code just create a new chart, despite if there are other charts already on the sheet (and just leave them alone).
The AutoPlot code I have is the following:
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 = xlAutomatic
End With
.MarkerStyle = xlNone
.Smooth = True
End With
Next ar
End With
Range("A1").RemoveSubtotal
End Sub
Any and all help is appreciated immensely,
Myles Mc