I have a worksheet which is trying to assign a defined scale to the Y axis pivot chart (for uniformity purposes - as there will be hundreds of charts overall). All charts will have a minimum value of zero. All charts will have 5 major increments.

The top value for each chart will be either 5%, 10%, 20%, 50% or 75%, depending on the highest value within the pivot table.

To start, I created a formula to calculate the maximum value for each chart. I then created a macro to be able to set an individual pivot chart axis based on the pivot table values (one of 5 macro's depending on the axis required - 5, 10, 20, 50, 75)

Because there are so many charts, I would prefer a macro which would set the axis based on the pivot table value automatically, and then loop through to do the same for each chart on the sheet.

I've re-written the macro a number of times, changing expressions, I even had help from chat GPT - but nothing seems to work.

I'd really appreciate if someone was able to assist

Here is the script -

Sub SetPivotChartXAxis()

Dim ws As Worksheet

Dim cht As ChartObject

Dim pvt As PivotTable

Dim fld As PivotField

Dim maxValue As Double

Dim scaleValue As Integer

Set ws = ActiveSheet

For Each cht In ws.ChartObjects

Set pvt = ws.PivotTables(cht.Chart.PivotLayout.PivotTable.Name)

Set fld = pvt.PivotFields("Values")

maxValue = WorksheetFunction.Max(cht.Chart.Axes(xlValue).MaximumScale)

If maxValue < 0.05 Then

scaleValue = 1

ElseIf maxValue < 0.1 Then

scaleValue = 2

ElseIf maxValue < 0.2 Then

scaleValue = 3

ElseIf maxValue < 0.5 Then

scaleValue = 4

ElseIf maxValue < 0.75 Then

scaleValue = 5

Else

scaleValue = 6

End If

With cht.Chart.Axes(xlCategory)

.CategoryType = xlCategoryScale

.TickLabels.NumberFormat = "0%"

.TickLabels.Orientation = xlUpward

.TickLabels.ReadingOrder = xlContext

.TickLabels.Font.Size = 8

.MinimumScale = 0

.MaximumScale = maxValue

.MajorUnit = (fld.PivotItems(2).Value - fld.PivotItems(1).Value) * scaleValue

End With

Next cht

End Sub