A lot of VBA examples on the net use Activexxx to refer to various objects. It always befuddles me since that breaks easily (something else, maybe the user, may change what is actually active).
Possibly, the ActiveSheet is not a worksheet to which a Chart object can be added. Rather than using ActiveSheet get the reference to the sheet you actually want to use:
ws = wb.Sheets.Item(sheetnameornumber);
chart = ws.ChartObjects.Add
If that still doesn't work, then I would break down the expression to see which part actually fails:
worksheets = wb.Worksheets; %get the worksheet collection
ws = worksheets.Item(nameorindex); %get a specific worksheet
chartobjects = ws.ChartObjects; %get the chartobject collection of the worksheet
chart = chartobjects.Add; %add a chart
