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:
#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
Report_Month_is_Last_Month_on_X_Axis.xlsm
40KB
Binary
Last modified 2yr ago