Set Legend Text Pie Chart

Question

I have been trying to set my two legends using a VBA Macro.

I want to change my two legends from 1 and 2 to Male and Female on my Pie Chart.

I can change it manually by setting it in the "Select Data Source" dialog by setting "Category (X) axis labels" to "=Sheet1!$B$6:$C$6" (which is Male and Female)

How do I do this with VBA, though?

Here is my code...

Sub Pie()

    For X = 7 To 13
        Charts.Add
        ActiveChart.ChartType = xlPie
        ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("b" & X & ":c" & X)
        ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
        ActiveChart.HasTitle = True
        CellVal = Worksheets("Sheet1").Range("A" & X).Value

        ActiveChart.ChartTitle.Text = "History statistics of " & CellVal

    Next X

End Sub

Answer 1

Pleas change the line with .SetSourceData method into this one:

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("b6:c6," & "b" & x & ":c" & x)