The Workbook object has a Charts property that returns the Charts collection of all chart sheets in the workbook. We can use the Add method of the Charts collection to create and add a new chartsheet to the workbook.
The syntax for the Add method is:
ChartsObject .Add(Before, After, Count)
As usual, this method returns a Chart object. The Before parameter specifies the sheet before which the new sheet is added, and the After parameter specifies the sheet after which the new sheet is added. Only one of these parameters can be specified at one time or an error will result. If neither is set, the new chart is inserted before the active sheet.
The optional Count parameter is an integer that specifies the number of sheets to be added. The default value is 1 .
For example, the following code creates a new chart sheet named "Sales":
Dim ch As Chart
Set ch = ThisWorkbook.charts.Add() ch.Name = "Sales"
The Add method cannot be used to format the chart. As mentioned earlier, this must be done using the various properties of the Chart object or the ChartWizard method, discussed later in the chapter.
Was this article helpful?