Automate Chart Creation Using Excel Macros VBA

Home
Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010



A question from a website visitor: I need few examples to create macros in one Excel sheet tab to form graph or chart in next sheet tab of same excel workbook. Could you please help me to achieve same through EXCEL MACROS?Thanks in advance.
Chocks Lingam

From the developer tab go to the controls group and click on the down arrow of 'insert' and from the Activex controls select a command button. Click and drag to create a command button under the data.
Click on the 'Design Mode' in the controls group and then click the command button to select it and finally the 'properties'option in the 'controls' group. Now change the caption of the command button to 'create chart'.
Next click on 'view code' also in the controls group of the developer tab and add the following code between the 'sub' and 'end sub' and your final code will look like this:
Private Sub CommandButton1_Click()
' First we select the names of the students along with the header 'Name of student' and the Average Marks also with the header
    Range("A1:A7,F1:F7").Select
'In the active sheet we add a chart
    ActiveSheet.Shapes.AddChart.Select
' We set the source data for the chart
    ActiveChart.SetSourceData Source:=Range( _
        "'Sheet1'!$A$1:$A$7,'Sheet1'!$F$1:$F$7")
'We define the type of chart
    ActiveChart.ChartType = xlColumnClustered
' Before we can perform an action on the chart we need to activate it
    ActiveSheet.ChartObjects(1).Activate
'We perform the cut operation
    ActiveSheet.ChartObjects(1).Cut
'we select the Sheet2 where we wish to paste our chart
    Sheets("Sheet2").Select
'We now paste the chart in the Sheet2 whic has become the active sheet after selection
    ActiveSheet.Paste
'we return to sheet1
    Sheets("Sheet1").Select
' we select the cell F9 in sheet1
    Range("F9").Activate
End Sub

The code to decide Grades of the students is given here for reference:
=IF(F2>=80,"A",IF(F2>=70,"B",IF(F2>=60,"C",IF(F2>=50,"D","Work Harder"))))

Watch the training video below to learn how to automate the creation of charts using macros (VBA) in Microsoft Excel:






Home
Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010