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:$Aquot; & 27 + [A20]
    ActiveChart.FullSeriesCollection(1).Values = "=Graphic!$B$27:$Bquot; & 27 + [A20]
#Else
    ActiveChart.SetSourceData Source:=wsGraphic.Range("A26:B" & 27 + [A20])
#End If
​
End Sub
Report_Month_is_Last_Month_on_X_Axis.xlsm
40KB
Binary

sbZip

UniqRandInt

Last modified 2yr ago

Copy link