|
Macro -- repetitive tasks
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 |
try something like
for i = 1 to 20 chnm="Chart_"&i ActiveWindow.Visible = False Windows("autoplot.xls").Activate ActiveSheet.ChartObjects(i)Activate activechart.name=chnm 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 next i "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 |
| All times are GMT +1. The time now is 08:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com