Microsoft Office Tutorials and References
In Depth Information
Adding a Chart to a Chart Sheet
Adding A cHArT To A cHArT sHEET
As you know, a chart sheet is a special kind of sheet in your workbook that contains only a chart.
If the chart is destined to be large and complicated, users often prefer such a chart be on its own
sheet so they can view its detail more easily.
Figure 14-1 shows a table of sales by month for a company that will be the source data for this chart
example. The table is on Sheet1 and although you can correctly refer to the source range in your
code as A1:B13, I prefer using the CurrentRegion property to reduce the chances of entering the
wrong range reference in my code.
The following macro creates a column chart for a new chart sheet based on the data in Figure 14-1.
If the Location property of your Chart object has not been specified, as it has not been in this
macro, your chart will be created in its own chart sheet. The result of this new chart sheet is shown
in Figure 14-2.
Sub CreateChartSheet()
‘Declare your chart type object variable
Dim myChart1 As Chart
‘Set your variable to add a chart
Set myChart1 = Charts.Add
‘Define the new chart’s source data
myChart1.SetSourceData _
Source:=Worksheets(“Sheet1”).Range(“A1”).CurrentRegion, _
‘Define the type of chart
myChart1.ChartType = xlColumnClustered
‘Delete the legend because it is redundant with the chart title.
End Sub
figurE 14-2
figurE 14-1
Search JabSto ::

Custom Search