Below is a portion of the macro (that I have cropped, original version is
much longer but a lot of this is repetitive statement) that allow me to
change the chart setting for a chart. I intend to change the setting for
about 20 charts in a worksheet

There must be a way to shorten this macro €“ retaining the main body of the
macro but replacing the chart Name (in bold, big fonts) with N then define

N= CHART_1, CHART_2, CHART_3, CHART_4 Etc etc

Let the macro run for CHART_1 until the last CHART and stop there.

By doing so the macro will be shorter and neat.

Question: how to do write a macro for this repetitive task?

Thanks

ActiveWindow.Visible = False
Windows("autoplot.xls").Activate
ActiveSheet.ChartObjects("CHART_1").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 2000
.MaximumScale = 3000
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("autoplot.xls").Activate
ActiveSheet.ChartObjects("CHART_2").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 2000
.MaximumScale = 3000
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("autoplot.xls").Activate
ActiveSheet.ChartObjects("CHART_3").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 2000
.MaximumScale = 3000
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("autoplot.xls").Activate
ActiveSheet.ChartObjects("CHART_4").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 2000
.MaximumScale = 3000
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

use something like

Dim cho as ChartObject

For Each cho in ActiveSheet.ChartObjects
cho.activate
--your code here--
Next cho


"OTS" wrote:

Below is a portion of the macro (that I have cropped, original version is
much longer but a lot of this is repetitive statement) that allow me to
change the chart setting for a chart. I intend to change the setting for
about 20 charts in a worksheet

There must be a way to shorten this macro €“ retaining the main body of the
macro but replacing the chart Name (in bold, big fonts) with N then define

N= CHART_1, CHART_2, CHART_3, CHART_4 Etc etc

Let the macro run for CHART_1 until the last CHART and stop there.

By doing so the macro will be shorter and neat.

Question: how to do write a macro for this repetitive task?

Thanks

ActiveWindow.Visible = False
Windows("autoplot.xls").Activate
ActiveSheet.ChartObjects("CHART_1").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 2000
.MaximumScale = 3000
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("autoplot.xls").Activate
ActiveSheet.ChartObjects("CHART_2").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 2000
.MaximumScale = 3000
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("autoplot.xls").Activate
ActiveSheet.ChartObjects("CHART_3").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 2000
.MaximumScale = 3000
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("autoplot.xls").Activate
ActiveSheet.ChartObjects("CHART_4").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 2000
.MaximumScale = 3000
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With