Excel VBA - Automatically Adjust Pivot Chart Min/Max

[camccull]

I am trying to create a macro that automatically updates the y-axis min/max for two different pivot charts that are on the same sheet. I have 50 sheets in my workbook that I need to do this for.

Does anyone know VBA for this?

Thanks,
Claire

Using:
Excel 2016
Windows 7

 

[Domenic]

Try the following macro, which assumes that the workbook contain your pivot charts is the active workbook, and that cells B1 and B2 in Sheet1 contain the minimum and maximum values. Note that it doesn't contain any error handling.

Option Explicit

Sub AdjustAllPivotCharts()

    Dim ws As Worksheet
    Dim oChrtObj As ChartObject
    Dim Min As Double
    Dim Max As Double
    
    Min = Worksheets("Sheet1").Range("B1").Value
    Max = Worksheets("Sheet1").Range("B2").Value
    
    For Each ws In ActiveWorkbook.Worksheets
        For Each oChrtObj In ws.ChartObjects
            With oChrtObj.Chart.Axes(Type:=xlValue, AxisGroup:=xlPrimary)
                .MinimumScale = Min
                .MaximumScale = Max
            End With
        Next oChrtObj
    Next ws
    
End Sub

In addition, to exclude a worksheet, let's say Sheet1, you can do the following...

    For Each ws In ActiveWorkbook.Worksheets
        [COLOR=#ff0000]If ws.Name <> "Sheet1" Then[/COLOR]
            For Each oChrtObj In ws.ChartObjects
                With oChrtObj.Chart.Axes(Type:=xlValue, AxisGroup:=xlPrimary)
                    .MinimumScale = Min
                    .MaximumScale = Max
                End With
            Next oChrtObj
       [COLOR=#ff0000] End If[/COLOR]
    Next ws

Hope this helps!

 

[camccull]

Thank you so much! I will try this out and let you know how it works. If there were multiple sheets that I did not want the macro to run on, say 'Sheet2' and 'Sheet3', how would I add them to the code?

 

[Domenic]

Try...

    For Each ws In ActiveWorkbook.Worksheets
        [COLOR=#ff0000]If ws.Name <> "Sheet2" And ws.Name <> "Sheet3" Then[/COLOR]
            For Each oChrtObj In ws.ChartObjects
                With oChrtObj.Chart.Axes(Type:=xlValue, AxisGroup:=xlPrimary)
                    .MinimumScale = Min
                    .MaximumScale = Max
                End With
            Next oChrtObj
        End If
    Next ws

 

[camccull]

Will do. Thank you!