Microsoft Office Tutorials and References
In Depth Information
Creating a chart from data on different worksheets
ChtLeft = 1
ChtHeight = 180
ChtWidth = 300
Application.ScreenUpdating = False
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> ActiveSheet.Name Then
LastRow = Sht.Range(“B65536”).End(xlUp).Row
Set ChtObj = ActiveSheet.ChartObjects.Add _
(ChtLeft, ChtTop, ChtWidth, ChtHeight)
‘Add a series
ChtObj.Chart.SeriesCollection.NewSeries
With ChtObj.Chart.SeriesCollection(1)
.Values = Sht.Range(Sht.Cells(LastRow, 2), Sht.Cells(LastRow, 4))
.XValues = Sht.Range(“B3:D3”)
.Interior.ColorIndex = 3
End With
With ChtObj.Chart
‘Specify chart type
.ChartType = xlColumnClustered
‘Adjust the gap width
.ChartGroups(1).GapWidth = 20
‘Remove color from Plot Area
.PlotArea.Interior.ColorIndex = xlNone
‘Set font size
.ChartArea.Font.Size = 9
‘Remove legend
.HasLegend = False
‘Add title
.HasTitle = True
.ChartTitle.Text = Sht.Range(“A1”)
‘Adjust scale
.Axes(xlValue).MinimumScale = 0
.Axes(xlValue).MaximumScale = 120000
‘Modify gridlines
.Axes(xlValue).MajorGridlines.Border.LineStyle = xlDot
End With
ChtTop = ChtTop + ChtHeight
End If
Next Sht
Application.ScreenUpdating = True
End Sub
The macro starts by declaring the variables that are used. The ChtTop variable is
used to position the charts (they are stacked vertically). The code then loops through
each worksheet, but skips the active worksheet (the one that will contain the charts).
Search JabSto ::




Custom Search