Set Major Unit on Chart using VBA

[PJGallagher]

Hi all,

Newbie here...

I have a spreadsheet file containing two sheets (Sheet1 & Sheet2).

Sheet2 is populated with some raw data pulled in from a .CSV file daily. The data range grows in size over time, daily stats for my work.

I produce a dashboard (Sheet1) showing 3 different charts that each point to specific data in Sheet2.

The charts auto populate daily (VBA runs when user clicks a button) showing data up to the latest entry in Sheet2.

PROBLEM: The issue is the X Axis on the charts changes from the default of 1 to a large number which seems to relate to the number of rows being referenced. So, the real dates are not displayed on the X Axis, just the first date and no more dates.

If I manually change the chart X Axis Major Unit to 1 (or click reset on the chart formating) the correct dates are shown on X Axis.

QUESTION: How can I set the X Axis Major Unit to 1 month in VBA? I have 3 charts on Sheet1 that I need to make this work for...

Many thanks in advance

Paul G

 

[Worf]

Hi and welcome to the Board

Sub All_Charts_on_Sheet()
Dim cho As ChartObject
For Each cho In Sheets("sheet3").ChartObjects
    cho.Chart.Axes(xlCategory).MajorUnit = 1
    'cho.Chart.Axes(xlCategory).MajorUnitIsAuto = True
Next
End Sub

 

[PJGallagher]

Works perfect - thank you so much