Creating a chart/graph in Excel using VBA?

[wharton05]

I have been struggling with this problem for days now, as I have been trying to teach myself how to write something in VBA. Thanks in advance for your help!

I am trying to create a graph in Excel 2007 that is taken from data that is exported each day. A couple of nuances: (i) while columns stay the same, the rows of data change daily, (ii) the data being graphed is contiguous in rows, but is NOT contiguous in columns, (iii) a total of 5 columns need to be graphed, one for the x-axis labels and two on the first y-axis and two on a 2nd y-axis, (iv) the data being dumped into the file is sorted first by a macro, and then subsequently split into numerous new worksheets containing a relevent set of data (think dumping sales info about multiple products into one sheet, and then the macro sorts and splits into new sheets data about one unique product)...so my concern is that definitions (my "LastRow2") may have to be reset for the loop of filtering and creating new sheets to work.

Here is the chart VBA I tried, but it fails miserably. Can anyone out there help?!?!? THANKS!!!

With ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=375, Top:=75, Height:=225)
.Chart.SetSourceData Source:=Sheets("Sheet1").Range("A3:G14")
.Chart.ChartType = xlLine
End With

Clear Series

With ActiveChart
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
End With

'Add X & Y

Dim LastRow2 As Integer
LastRow2 = Range("A" & Rows.Count).End(xlUp).Row

With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Range("H2")
.Values = ActiveSheet.Range("H3:H" & LastRow2)
.XValues = ActiveSheet.Range("D3:D" & LastRow2)
End With

With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Range("K2")
.Values = ActiveSheet.Range("K3:K" & LastRow2)
.XValues = ActiveSheet.Range("D3:D" & LastRow2)
End With

With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Range("I2")
.Values = ActiveSheet.Range("I3:I" & LastRow2)
.XValues = ActiveSheet.Range("D3:D" & LastRow2)
End With

With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Range("L2")
.Values = ActiveSheet.Range("L3:L" & LastRow2)
.XValues = ActiveSheet.Range("D3:D" & LastRow2)
End With

ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).AxisGroup = 2
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).AxisGroup = 2

 

[wharton05]

Anyone? Really in a bind here and would appreciate any input!