Automating Charts

• AddChart2 creates the chart shape itself — Style:=201 picks a built-in visual style, XlChartType:=xlColumnClustered picks a standard column chart, and Left/Top/Width/Height position and size it on the worksheet in points, the same unit Excel uses internally for shape placement;
• AddChart2 actually returns a Chart object, not the ChartObject container around it — the .Chart.Parent at the end of that line is what steps back up to the container, which is the type chartObj is declared as; this quirk is easy to forget and worth copying exactly;
• SetSourceData is what tells the otherwise-empty chart shape what data to plot — pointing it at tbl.ListColumns("Profit").Range means it charts the Profit column across every visible row of the Table;
• HasTitle = True has to be set before ChartTitle.Text is assigned — trying to set the title text on a chart that doesn't have a title turns on yet will fail.

1. Running BuildProfitChart

• Copy the Sub exactly as shown in chapter and run it — no changes needed for this part.
• You should see a chart appear on the Reports sheet plotting Profit for every row currently visible in the table.

2. Coloring bars and removing the legend

• Both properties belong to the chart object, not the worksheet — chartObj.Chart is your entry point, same as the formatting example in the chapter.
• The bar color lives on SeriesCollection(1), since there's only one data series being plotted (Profit) — .Format.Fill.ForeColor.RGB is the specific property to set.
• Removing the legend is a single Boolean property (HasLegend), separate from the fill color line.

3. Filtering and re-running SetSourceData

• Apply an AutoFilter on Month (Field:=1) restricted to "January" — the same technique from section 4.2.
• Then call SetSourceData again with the exact same tbl.ListColumns("Profit").Range expression from BuildProfitChart — nothing about that line needs to change.
• Watch closely what happens to the chart afterward: does it shrink to only January's five regions, or does it still show all fifteen rows including the ones AutoFilter just hid? That observation is the actual point of this task, not just running the code.

Option Explicit

' Point 1 — run this exactly as shown in the chapter
Sub BuildProfitChart()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim chartObj As ChartObject

    Set ws = ThisWorkbook.Worksheets("Reports")
    Set tbl = ws.ListObjects("tblReports")

    Set chartObj = ws.Shapes.AddChart2(Style:=201, _
        XlChartType:=xlColumnClustered, _
        Left:=400, Top:=20, Width:=400, Height:=250).Chart.Parent

    With chartObj.Chart
        .SetSourceData Source:=tbl.ListColumns("Profit").Range
        .HasTitle = True
        .ChartTitle.Text = "Profit by Region"
    End With
End Sub

' Point 2 — color the bars and remove the legend
Sub FormatProfitChart()
    Dim ws As Worksheet
    Dim chartObj As ChartObject

    Set ws = ThisWorkbook.Worksheets("Reports")
    Set chartObj = ws.ChartObjects(1)

    With chartObj.Chart
        .SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(24, 106, 60)
        .HasLegend = False
    End With
End Sub

' Point 3 — filter to January, then re-point the chart at the same range
Sub FilterJanuaryAndRefreshChart()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim chartObj As ChartObject

    Set ws = ThisWorkbook.Worksheets("Reports")
    Set tbl = ws.ListObjects("tblReports")
    Set chartObj = ws.ChartObjects(1)

    tbl.Range.AutoFilter Field:=1, Criteria1:="January"

    chartObj.Chart.SetSourceData Source:=tbl.ListColumns("Profit").Range
End Sub

Run these in order: BuildProfitChart, then FormatProfitChart, then FilterJanuaryAndRefreshChart. For point 3 specifically — pay attention to what actually happens. Excel charts generally do respect an active AutoFilter, hiding the filtered-out rows' bars automatically, even without re-running SetSourceData at all. Re-running it here mostly just confirms the chart is still correctly pointed at the full column — the filter itself is what's doing the visual hiding, not the SetSourceData call. Worth testing with the filter both on and off to see the difference for yourself.