Using macro to run repetitive tasks

O

OTS

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
 
D

Duke Carey

use something like

Dim cho as ChartObject

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top