Copy Excel Line Chart (Graph) Using VBA

From this lesson you can learn how to copy a chart using VBA. Let’s consider this sample excel line chart. Chart is on an activesheet.

Assume we want to create a copy of that chart in the same sheet. We can do that as follows.

Sub CopyChart()      Dim WS As Worksheet      Set WS = ActiveSheet      WS.ChartObjects("Chart 4").Chart.ChartArea.Copy      WS.Range("N5").Select      WS.Paste End Sub

In the above macro we used “WS.Range("N5").Select” to select the N5 cell before paste the chart. Therefore the chart's top left corner was positioned on that cell.

However If the user has selected the chart or if the ChartObject(which contains the chart) has activated, then we can create a copy of the chart like this.

Sub CopyChart_Ex2()      ActiveChart.ChartArea.Copy      Range("I10").Select      ActiveSheet.Paste End Sub

Now let’s look at how we can copy charts to a different sheet. Assume we have a workbook with two sheets called “Sheet1” and “Sheet2” in “Sheet1” containing a chart named “Chart 4”. Below macro will copy “Chart 4” from “Sheet1” to “Sheet2”.

Sub CopyChartToDifferentSheet()      Dim WS_Current As Worksheet      Dim WS_New As Worksheet      Set WS_Current = Worksheets("Sheet1")      Set WS_New = Worksheets("Sheet2")      WS_Current.ChartObjects("Chart 4").Chart.ChartArea.Copy      WS_New.Paste End Sub

Formatting Excel Line Charts Using VBA

We often use Excel charts in our worksheets. They are really important no matter for which field your spreadsheet is related to. Because charts can graphically represent data which helps to gain accurate understanding of the data. From our earlier lessons we learnt how to add charts with one series or multiple series automatically using vba. Check out these posts if you want to learn how.

Create a line chart (with one series)

Create line chart with multiple series

So today we are going to look at how we can format a line chart automatically using VBA. Here is some sample data I have in my excel sheet.

So if we insert a line chart in Excel 2013, then the default chart will look like this.

However there are lots of styles you can choose from for your chart. This is how you can change the style of your chart using VBA.

Sub ChartStyle33()      Dim Cht As Chart      Set Cht = ActiveSheet.ChartObjects("Chart 1").Chart      Cht.ChartStyle = 33 End Sub

If we run the above code, the chart will change to something like this.

Note that, in the above code we refer to the chart using it’s name. So you will need to change the “Chart 1” with the name of your chart. Don’t know how to find the name of a chart? This post explain how to find the name of a chart manually.

Find the name of a chart in Excel

Read this post if you want to find the name of the active chart using VBA.

How to find the name of an active chart using VBA

In the above macro, we changed the style of the chart using it’s name. However instead of the name we can refer to the chart using ActiveChart property, if we want to change the style of the active chart. Then we can rewrite the above code as follows.

Sub ChartStyle43()      Dim Cht As Chart      Set Cht = ActiveChart      Cht.ChartStyle = 43 End Sub

Note that I used a different number for the Cht.ChartStyle in this example. So the result will look like this.

Now you may have thought what are the numbers you can use for Cht.ChartStyle. You can use numbers from 1 to 48 for the Cht.ChartStyle. In addition to those styles, the following are also available in Excel 2013.

    342
    332
    239
    237
    236
    235
    234
    233
    232
    231
    230
    228
    227

How to find the name of an active chart using VBA

In an earlier lesson we looked at how to create a chart automatically. Read this post if you want to know how.

How to create a line chart automatically - Excel VBA

Today I’m going to teach you how to find the name of an active chart using VBA. When you create a chart either manually or automatically using VBA then it becomes active chart until you deselect it. Also if you select an existing chart in an excel sheet, then it also becomes active chart. Assume we have an active chart like this in our excel sheet.

Name of the sheet is “Sheet4”. Now let’s try to find the name of the chart using VBA. So you might think we can easily find the name of this active chart as follows.

Sub ActiveaChartName() Debug.Print ActiveChart.Name End Sub

But this doesn’t work. This is what we will get if we run above code.

We have an extra name addition to the chart name in the beginning. That extra name is the sheet name. So we need to do some additional work to extract the chart name. Make sure you have selected at least one chart when you run above code. Otherwise you will get below run-time error.

So now let’s see how we can extract only the chart name from the ActiveChart.Name. If we analyze the default chart names given for the charts when we create them, we can see that it has the following format.

Chart + ChartNumber

For example, Excel names the charts like this. Chart 1, Chart 2, Chart 3 etc. So we can use the split function to extract the chart name from the ActiveChart.Name easily.

Sub ActiveaChartName() Dim SheetAndChartName As String Dim ChartName As String Dim WrdArray() As String SheetAndChartName = ActiveChart.Name WrdArray() = Split(SheetAndChartName) ChartName = WrdArray(UBound(WrdArray) - 1) & " " & WrdArray(UBound(WrdArray)) Debug.Print ChartName End Sub

If you want to learn more about split function, read this post.

Split Function

Above macro will print the chart name correctly in the immediate window as follows.

Note that this will only work if the user hasn’t altered the default chart name.

Find the name of a chart in Excel

Did you know that whenever you create a chart in Excel a unique name is given to it at the time of creation. This name is not important to you if you are only doing manual work with the data and charts. But knowing the name might be very important if you do any automations related to charts using VBA. Because if we know the name we can directly reference that chart in our codes. So in this post I will show you how to find the name of a chart in Excel.

This excel sheet contains a table and a chart. Now let’s see how to find the name of the chart. To find that, first we need to select the chart of which we need to find the name. Here we have only one chart. If you have more than one, you need to select only the one you want to find the name of.

Above image shows how Excel changed when we selected the chart. It marks the data in the table which is related to the selected chart. And also as you can see two new tabs appeared at the end of the tabs. These two tabs are “DESIGN” and “FORMAT” tabs. To find the name of the chart you need to select the Format tab. Then click on the “Selection Pane” in the “Arrange” group of the Format tab.

When you click on the “Selection Pane”, that pane will appear on the right side of the excel sheet. Also the name of the chart will be highlighted like this.

In this example we have only one chart. But if we have multiple charts then only the selected chart will be highlighted. For an example consider the below image of a Selection Pane. According to this pane this excel sheet has 4 charts and only the “Chart 8” is selected.

Create line chart with multiple series using Excel VBA

From one of our earlier posts we learnt how to create a line chart with one series automatically using VBA. Read this post if you want to know how.

How to create a line chart automatically - Excel VBA

In this post I will show you how we can create line charts with multiple series using VBA. Let’s consider this sample table. For this data we need to create a line chart with 2 series.

As you can see we have our data in range A1:C13. So when this sheet is the active sheet, we can create 2 series line chart for this data as follows.

Sub CreateChart_2Series()      Dim WS As Worksheet      Set WS = ActiveSheet      WS.Range("A1:C13").Select      ActiveSheet.Shapes.AddChart2(227, xlLine).Select End Sub

Note that you need to replace the A1:C13 with the range you want to create the line chart for. Here is the result of the above macro.

This is another sample table for which we can create a line chart with 3 series.

To create a line chart for all the data, the only change we need to do for our first macro is changing the range from A1:C13 to A1:D13.

Sub CreateChart_3Series()      Dim WS As Worksheet      Set WS = ActiveSheet      WS.Range("A1:D13").Select      ActiveSheet.Shapes.AddChart2(227, xlLine).Select End Sub

Line chart with 3 series.