excel worksheets.add xlchart

I am trying to create a new worksheet of type xlChart (-4109) using the Excel COM interface.
The .add method allows me to add 3/4 types of sheets.

     xlDialogSheet = -4116
     xlExcel4IntlMacroSheet = 4
     xlExcel4MacroSheet = 3
     xlWorksheet = -4167

But the com interface throws an error when I try to create the  xlChart (xlChart = -4109) type.

Any suggestions on creating a xlChart type sheet in code?
Who is Participating?
Rory ArchibaldCommented:
Use the Workbook's Charts.Add method.
MSmaxImplementation ConsultantCommented:
See below code for 2 other options which can be underlined by any com code

Sub AddNewChart()
Dim x As Variant
Set x = ThisWorkbook.Sheets.Add(Type:=xlChart)
x.Name = "NewChart"
Set x = ThisWorkbook.Charts.Add
x.Name = "NewChart1"

End Sub

Open in new window

norbs101Author Commented:
The Charts.Add method worked perfectly!

Thank you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.