Managing Chart Sheets In Excel VBA

Below are three procedures for copying, deleting, and exporting a chart sheet.

Copying a Chart Sheet:

Sub CopyChartSheet()
    ThisWorkbook.Charts("Chart1").Copy After:=Worksheets("Sheet3")
    ActiveChart.Name = "Chart1 Copy"
End Sub

Explanation:
A chart sheet can be copied just like a worksheet. The Copy() method creates a copy of the specified chart sheet, as shown in Figure 7.6. Using the Before or After parameters, you can specify the location of the copied sheet within the workbook. Without these parameters, Excel would create a new workbook containing only the copied chart sheet.

Deleting a Chart Sheet:

Sub DeleteChartSheet()
    ThisWorkbook.Charts("Chart1 Copy").Delete
End Sub

Explanation:
The Delete() method removes the specified chart sheet. Before deletion, Excel prompts the user for confirmation.

Exporting a Chart Sheet as an Image:

Sub ExportChartSheet()
    ThisWorkbook.Charts("Chart1").Export "C:\Users\POPOLY\Desktop\img.png"
End Sub

Explanation:
The Export() method generates an image file of the chart sheet. You must provide the filename, optionally including the directory path. The file extension determines the image format. Commonly supported formats include .jpg, .gif, and .png. The result is illustrated in Figure 7.7.