CreateChartInAnotherSheet

Sub CreateChartInAnotherSheet()
    Dim wsAP As Worksheet, wsAR As Worksheet, wsDash As Worksheet
    Dim co As ChartObject
    Dim cht As Chart

    ' Sheets
    Set wsAP = ThisWorkbook.Sheets("AP")              ' Expenditure
    Set wsAR = ThisWorkbook.Sheets("AR Invoice")      ' Revenue
    Set wsDash = ThisWorkbook.Sheets("exec dasboard") ' Target (check spelling)

    ' Optional: replace existing chart with the same name
    On Error Resume Next
    wsDash.ChartObjects("RevVsExp").Delete
    On Error GoTo 0

    ' Create chart ON the dashboard
    Set co = wsDash.ChartObjects.Add(Left:=30, Top:=30, Width:=500, Height:=300)
    co.Name = "RevVsExp"
    Set cht = co.Chart

    With cht
        .ChartType = xlLine
        .HasLegend = True
        .SetElement msoElementLegendRight

        ' Expenditure
        With .SeriesCollection.NewSeries
            .Name = "Expenditure"
            .Values = wsAP.Range("I2:I51")
            .XValues = wsAP.Range("E2:E51")
        End With

        ' Revenue
        With .SeriesCollection.NewSeries
            .Name = "Revenue"
            .Values = wsAR.Range("I2:I51")
            .XValues = wsAR.Range("E2:E51")
        End With

        .HasTitle = True
        .ChartTitle.Text = "Revenue vs Expenditure"

        With .Axes(xlCategory)
            .HasTitle = True
            .AxisTitle.Text = "Months"
        End With
        With .Axes(xlValue)
            .HasTitle = True
            .AxisTitle.Text = "Php"
        End With
    End With
End Sub