VBA Excel Set Horizontal Axis on Chart

Question

I have compiled the following macro to create 3 charts on the right side of the Excel Worksheet.

Sub CreateChart()
Dim objChart As ChartObject
Dim myChtRange As Range
Dim myDataRange As Range
With ActiveSheet

Set myChtRange = Range("M10:Q23")
' What range contains data for chart
Set myDataRange = ActiveSheet.ListObjects("Table2").Range
' Cover chart range with chart
Set objChart = .ChartObjects.Add( _
    Left:=myChtRange.Left, Top:=myChtRange.Top, _
    Width:=myChtRange.Width, Height:=myChtRange.Height)
' Put all the right stuff in the chart
With objChart.Chart
    .ChartArea.AutoScaleFont = False
    .ChartType = xlLine
    .ChartStyle = 245
    .SetSourceData Source:=myDataRange
    .HasTitle = True
    .HasLegend = False
    .ChartTitle.Characters.Text = "PO By Year"
    .ChartTitle.Font.Bold = True
    .ChartTitle.Font.Size = 12

    With .Axes(xlCategory, xlPrimary)
       .HasTitle = True
       With .AxisTitle
            .Characters.Text = "Year"
            .Font.Size = 10
            .Font.Bold = True
        End With
    End With
    With .Axes(xlValue, xlPrimary)
        .HasTitle = True
        .DisplayUnit = xlMillions
        .HasDisplayUnitLabel = False
        With .AxisTitle
            .Characters.Text = "Millions"
            .Font.Size = 10
            .Font.Bold = True
        End With
    End With
End With

Set myChtRange = Range("S10:W23")
' What range contains data for chart
Set myDataRange = ActiveSheet.ListObjects("Table17").ListColumns(5).Range
' Cover chart range with chart
Set objChart = .ChartObjects.Add( _
    Left:=myChtRange.Left, Top:=myChtRange.Top, _
    Width:=myChtRange.Width, Height:=myChtRange.Height)
' Put all the right stuff in the chart
With objChart.Chart
    .ChartArea.AutoScaleFont = False
    .ChartType = xlLine
    .ChartStyle = 245
    .SetSourceData Source:=myDataRange
    .HasTitle = True
    .HasLegend = False
    .ChartTitle.Characters.Text = "Invoices By Year"
    .ChartTitle.Font.Bold = True
    .ChartTitle.Font.Size = 12
    With .Axes(xlCategory, xlPrimary)
        .HasTitle = True
        With .AxisTitle
            .Characters.Text = "Years"
            .Font.Size = 10
            .Font.Bold = True
        End With
    End With
    With .Axes(xlValue, xlPrimary)
        .HasTitle = True
        .DisplayUnit = xlMillions
        .HasDisplayUnitLabel = False
        With .AxisTitle
            .Characters.Text = "Millions"
            .Font.Size = 10
            .Font.Bold = True
        End With
    End With
End With

Set myChtRange = Range("Y10:AC23")
' What range contains data for chart
Set myDataRange = ActiveSheet.ListObjects("Table30").ListColumns(5).Range
' Cover chart range with chart
Set objChart = .ChartObjects.Add( _
    Left:=myChtRange.Left, Top:=myChtRange.Top, _
    Width:=myChtRange.Width, Height:=myChtRange.Height)
' Put all the right stuff in the chart
With objChart.Chart
    .ChartArea.AutoScaleFont = False
    .ChartType = xlLine
    .ChartStyle = 245
    .SetSourceData Source:=myDataRange
    .HasTitle = True
    .HasLegend = False
    .ChartTitle.Characters.Text = "Req to PO"
    .ChartTitle.Font.Bold = True
    .ChartTitle.Font.Size = 12
    With .Axes(xlCategory, xlPrimary)
        .HasTitle = True
        With .AxisTitle
            .Characters.Text = "Time"
            .Font.Size = 10
            .Font.Bold = True
        End With
    End With
    With .Axes(xlValue, xlPrimary)
        .HasTitle = True
        .DisplayUnit = xlMillions
        .HasDisplayUnitLabel = False
        With .AxisTitle
            .Characters.Text = "Days"
            .Font.Size = 10
            .Font.Bold = True
        End With
    End With
End With
End With
End Sub

The Table I'm referencing for the first chart looks like this:

And the resulting Charts look like this:

The issue is I cannot determine how to get the years to show on the horizontal axis instead of Points 1,2,3,4....

Do I need to reference the entire table as I have done for table 2, or do I need to reference just the 5th column as I have done for Table 17?

Macro recorder doesn't show the steps to go from the pictured chart to a chart with the years on the horizontal axis.

Answer 1

have you tried something like this:

ActiveChart.SeriesCollection(1).XValues = "='Sheet1'!$A$1:$A$4"

Where SeriesCollection(1) is the name of the series, and of course using the range where the years actually are instead of Sheet1 etc.