I am having trouble creating a macro to automatically adjust maximumscales for multiple sheets on dynamic basis.

Basically the Axis X is a date. It ends with last data's date which is today. I am trying to make it today()+5, so the end of graph could display better(leaving a space between the edge of graph and the line.

I am doing currently using

Sub adjustscales()

'Worksheet Name".Chartobjects("Chart 2").Chart.Axes(xIvalue).MaximumScale = today() + 5

End Sub

It gets s compile, which as a starter, I am not sure how to resolve, not to mention it is probably not working.