How to generate multiple line charts VBA
Category: [Excel] Demo Available
How to generate multiple line charts VBA
Requirement:
The user is trying to automatically generate multiple charts using VBA.
Solution:
Use this, with the original data layout:
Code:
Sub CreateCharts()
Dim ws As Worksheet
Dim ch As Chart
Dim NumCharts As Integer, ChartName As String, ChartTitle As String, i As Integer
Set ws = Sheets("Chart_data")
NumCharts = WorksheetFunction.CountA(ws.Rows(2))
For i = 3 To NumCharts Step 2 '2 columns of data per chart
ChartName = ws.Cells(2, i) '"chrt" & Range(Col1 & 2)
ChartTitle = ws.Cells(2, i) 'Range(Col1 & 2)
Set ch = Charts.Add
With ch
.ChartType = xlLine
.SetSourceData Source:=ws.Range(ws.Cells(3, i), ws.Cells(27, i + 1)), _
PlotBy:=xlColumns
.SeriesCollection(1).XValues = ws.Range("B4:B27")
.SeriesCollection(2).XValues = ws.Range("B4:B27")
.Name = ChartName
.HasTitle = True
.ChartTitle.Characters.Text = ChartTitle
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Units"
End With
Next i
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by rory.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use VBA to returning 5 left digits and pasting to bottom of existing data set |
| How to download a file using VBA |
| How to use VBA script to count files/subfolders using a range from WB for the root folder |
| How to use VBA code to output multiple worksheets to separate workbooks |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.