Sulprobil
Search…
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:
1
#Const ApplicationVersion = 14 'Get at runtime with Val(Application.Version)
2
​
3
Sub SetXAxis2ReportMonth()
4
​
5
wsGraphic.ChartObjects("Diagram 1").Activate
6
With ActiveChart.Axes(xlCategory)
7
.MinimumScale = wsGraphic.[A22]
8
.MaximumScale = wsGraphic.[A18]
9
.MajorUnit = wsGraphic.[A21]
10
If DateDiff("m", [A17], [A18]) < 6 Then
11
.TickLabels.NumberFormat = "DD\/MM"
12
Else
13
.TickLabels.NumberFormat = "MM\/YY"
14
End If
15
End With
16
#If ApplicationVersion > 14 Then
17
ActiveChart.FullSeriesCollection(1).XValues = "=Graphic!$A$27:$Aquot; & 27 + [A20]
18
ActiveChart.FullSeriesCollection(1).Values = "=Graphic!$B$27:$Bquot; & 27 + [A20]
19
#Else
20
ActiveChart.SetSourceData Source:=wsGraphic.Range("A26:B" & 27 + [A20])
21
#End If
22
​
23
End Sub
Copied!
Report_Month_is_Last_Month_on_X_Axis.xlsm
40KB
Binary
Last modified 1yr ago
Copy link