Set last month on X axis to report month

Are you struggling to beautify your report because the last month on a line graphic is not your recent = report month?

Here is a simple solution:

You set the label count to the number of dates which you like to see before the report month on the X axis of your graphic. This approach is creating a new start date which is on or before the previous start date, so you might encounter a small gap between the Y axis and the start of your graph.

Enter the other formulas as shown above, and then you can beautify the graphic via VBA:

Please read my Disclaimer

#Const ApplicationVersion = 14 'Get at runtime with Val(Application.Version)

Sub SetXAxis2ReportMonth()

wsGraphic.ChartObjects("Diagram 1").Activate
With ActiveChart.Axes(xlCategory)
    .MinimumScale = wsGraphic.[A22]
    .MaximumScale = wsGraphic.[A18]
    .MajorUnit = wsGraphic.[A21]
    If DateDiff("m", [A17], [A18]) < 6 Then
        .TickLabels.NumberFormat = "DD\/MM"
    Else
        .TickLabels.NumberFormat = "MM\/YY"
    End If
End With
#If ApplicationVersion > 14 Then
    ActiveChart.FullSeriesCollection(1).XValues = "=Graphic!$A$27:$A$" & 27 + [A20]
    ActiveChart.FullSeriesCollection(1).Values = "=Graphic!$B$27:$B$" & 27 + [A20]
#Else
    ActiveChart.SetSourceData Source:=wsGraphic.Range("A26:B" & 27 + [A20])
#End If

End Sub

40KB

Report_Month_is_Last_Month_on_X_Axis.xlsm