1,数据形式如下,要做成的效果也如下:
需求:将每个人的成绩单独生成一张柱状图。
Sub 批量生成图表()
Dim mydatesourse As Range
For i = 2 To 5
Set mydatesoure = Sheets("学生成绩表").Range("a" & i & ":d" & i)
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=mydatesoure, PlotBy:=xlColumns
ActiveChart.Location where:=xlLocationAsObject, Name:=Sheets("学生成绩表").Name
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = Sheets("学生成绩表").Range("a" & i) & "同学的成绩单"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "成绩"
.SetElement (msoElementLegendTop)
.SeriesCollection(1).Name = "=学生成绩表!$B$1"
.SeriesCollection(2).Name = "=学生成绩表!$C$1"
.SeriesCollection(3).Name = "=学生成绩表!$D$1"
End With
ActiveChart.ChartArea.Select
Next
End Sub
2,如何批量全部删除图表,代码如下:
Sub 批量删除图表()
ActiveSheet.ChartObjects.Delete
End Sub