Creating a Chart Sheet In Excel VBA

Charts placed on their own sheets (called chart sheets) exist at the same hierarchical level as worksheets within a workbook. There are several collections within a workbook that list different types of sheets:

• Sheets – contains all worksheets and chart sheets
• Worksheets – contains only worksheets (as commonly known)
• Charts – contains only chart sheets

The following procedure creates a simple line chart as a new chart sheet within the workbook:

Sub CreateChartOnNewSheet()
    ThisWorkbook.Charts.Add After:=Worksheets("Sheet1")
    With ActiveChart
        .ChartType = xlLine
        .SetSourceData Worksheets("Sheet1").Range("A1:C8")
        .Name = "Chart1"
    End With
End Sub

Explanation:

The Add() method of the Charts object creates a new chart sheet and adds it to the Charts collection of the active workbook. Similar to copying or moving worksheets, you can specify the position of the new sheet using the Before or After parameters. If no position is specified, the new sheet is inserted before the currently active sheet.

The newly created chart sheet is of type Chart and automatically becomes the active chart sheet. Therefore, it can be accessed using ActiveChart.

The ChartType property sets the type of the chart—in this case, xlLine for a line chart. Some commonly used chart types are listed below.

The SetSourceData() method defines the data source for the chart. Here, the data range is cells A1 to C8 on the worksheet named « Sheet1, » which contains the temperature data.

You can assign a name to the chart using the Name property.

Note:
The SetSourceData() method has a second optional parameter that specifies whether the chart plots data by columns (xlColumns, which is the default) or by rows (xlRows).

Table 7.1 lists several chart types and their corresponding ChartType property values:

(Table 7.1: Chart Types)