VBA code to loop through all charts in each worksheet of a workbook

[athaung]

Hi,

I am trying to write code that allows me to go through each worksheet in my workbook (except a select few as shown in code below) and fix the y-scale of 4 different chart/graphs in EACH worksheet based on values in a worksheet. Below is the code I have so far. This changes the y-scale of all charts on the worksheet I am currently on, but does not cycle through all work sheets. Note that "Axis_min" and "Axis_max" are names I assigned using Excel: Formulas >Defined Names > Define Name. I used an =Indirect("A1") kind of formula so that the Axis_min and Axis_max will be different for each worksheet. Please help fix my code! I'll be forever grateful

Sub SizeGraphs()

Dim ws As Worksheet
Dim objCht As ChartObject

For Each ws In ThisWorkbook.Worksheets
        If ws.Name = "Master" Or ws.Name = "Template" Or ws.Name = "Start" Or ws.Name = "NQF 2015" Then
        
        Else
        
        For Each objCht In ActiveSheet.ChartObjects
            With objCht.Chart
            With .Axes(xlValue)
                .MaximumScale = ActiveSheet.Range("Axis_max").Value
                .MinimumScale = ActiveSheet.Range("Axis_min").Value
            End With
            End With
        Next objCht

    End If
Next ws
End Sub

 

[Domenic]

Try replacing...

For Each objCht In ActiveSheet.ChartObjects

with

For Each objCht In [COLOR=#ff0000]ws[/COLOR].ChartObjects

Hope this helps!

 

[athaung]

Thanks!

I ended up with the following code that works:

For Each ws In Worksheets
    If ws.Name = "Master" Or ws.Name = "Template" Or ws.Name = "Start" Or ws.Name = "NQF 2015" Then
        
        Else
        
        ws.Activate
        For Each objCht In ws.ChartObjects
            With objCht.Chart
                With .Axes(xlValue)
                    .MaximumScale = ActiveSheet.Range("Axis_max").Value
                    .MinimumScale = ActiveSheet.Range("Axis_min").Value
                End With
            End With
        Next objCht

    End If
Next ws

 

[Domenic]

You're very welcome!