Untitled

Sub CreateChart()

' Define the data range for the table and charts
Dim salesData As Range
Dim fixedCostsData As Range
Dim ebitData As Range
Dim targetData As Range
Set salesData = Range("A1:A12")
Set fixedCostsData = Range("B1:B12")
Set ebitData = Range("C1:C12")
Set targetData = Range("D1:D12")

' Insert a new table and set its data source to the defined ranges
Dim table As Table
Set table = ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:D12"), , xlYes)

' Insert a bar chart for the sales data
Dim salesChart As Chart
Set salesChart = ActiveSheet.ChartObjects.Add(Left:=50, Width:=400, Top:=50, Height:=300)
salesChart.Chart.SetSourceData Source:=salesData
salesChart.Chart.ChartType = xlColumnClustered

' Insert a bar chart for the fixed costs data
Dim fixedCostsChart As Chart
Set fixedCostsChart = ActiveSheet.ChartObjects.Add(Left:=50, Width:=400, Top:=350, Height:=300)
fixedCostsChart.Chart.SetSourceData Source:=fixedCostsData
fixedCostsChart.Chart.ChartType = xlColumnClustered

' Insert a bar chart for the EBIT data
Dim ebitChart As Chart
Set ebitChart = ActiveSheet.ChartObjects.Add(Left:=450, Width:=400, Top:=50, Height:=300)
ebitChart.Chart.SetSourceData Source:=ebitData
ebitChart.Chart.ChartType = xlColumnClustered

' Add a red line to each chart for the yearly target data
Dim series As Series
For Each series In salesChart.Chart.SeriesCollection
    series.Trendlines.Add Type:=xlLine
    series.Trendlines(1).Format.Line.ForeColor.RGB = RGB(255, 0, 0)
    series.Trendlines(1).DataLabels.ShowValue = True
Next

For Each series In fixedCostsChart.Chart.SeriesCollection
    series.Trendlines.Add Type:=xlLine
    series.Trendlines(1).Format.Line.ForeColor.RGB = RGB(255, 0, 0)
    series.Trendlines(1).DataLabels.ShowValue = True
Next

For Each series In ebitChart.Chart.SeriesCollection
    series.Trendlines.Add Type:=xlLine
    series.Trendlines(1).Format.Line.ForeColor.RGB = RGB(255, 0, 0)
    series.Trendlines(1).DataLabels.ShowValue = True
Next

End Sub