最简单的做法是copy一个事先创建好的图表，然后粘贴后，修改Series的数据来源定义，注意Copy/Paste的时候最好是Sleep一下，否则会有异常抛出

Sub CreateChart()
Dim Sheet As Worksheet
Dim NewChart As ChartObject
Dim i As Integer
Dim j As Integer

Dim index As Integer
Dim A As Integer
Dim B As Integer
Dim R As String

Set Sheet = Excel.Sheets(2)
index = 0
For i = 1 To 5
Sheet.ChartObjects(1).Copy
Sheet.Activate
Sheet.Paste
Set NewChart = Sheet.ChartObjects(i + 1)

NewChart.Name = "Chart" + CStr(i)

NewChart.Top = Sheet.ChartObjects(1).Top + i * 400
NewChart.Left = Sheet.ChartObjects(1).Left

For j = 1 To 2
NewChart.Chart.SeriesCollection(j).Values = "=Sheet1!$EG$" & CStr(index) & ":$EL$" & CStr(index)
index = index + 1
Next j

A = (index \ 16) * 16

R = Excel.Sheets(1).Range("A" & CStr(A)).Text

NewChart.Chart.ChartTitle.Text = R
Application.Wait (Now + TimeValue("0:00:05"))
Next i
End Sub