You are here:

Excel/same macro based on integer


hope you are keeping well.
I would like to use the same macro but based on an integer, is this possible?
for instance, if I wanted to call that same macro for 100 charts
each chart is called chart1, chart2,,,,,,chart100
is there a clever way to use the same code
or do I have to write 100 macro CopyChart1(), ,,,CopyChart100()

Sub CopyChart1()
dim i as integer
ActiveSheet.ChartObjects("Chart" & i).Activate
ActiveChart.CopyPicture _
   Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture

End Sub

I hope this is clear. I feel a bit stupid because it is a pretty basic question, but I never had the need before.
Thanks for your help.

ANSWER: Except for the fact that the default names are "Chart 1" (ie with a space) your macro would work fine - if it is to copy a specified chart, then referencing a cell on the worksheet or something would serve to set the value of i.  Having copied it, you would possibly need to automated the pasting part of the process - which should also be possible!

---------- FOLLOW-UP ----------

QUESTION: Sorry this is not what I meant.
I used this only as an example.
I have a macro that I need to repeat 100 time except that each time it is referencing some strings, ranges etc that I defined using an integer. So all the macro is the same except for the references
What I am after is:
Do I need to write the macro 100 times or can I for instance use a trick that will allow me to use the same macro, for instance
I cannot use the trick you suggested (referencing a cell on the worksheet or something would serve to set the value of i) because the macros are linked to checkboxes, so to get a different value of i, I would need to write a different macro for each checkbox (which is what I am trying to avoid). I cannot reference the True because the checkbox are not mutually exclusive.
If this is not clear, do not worry I will write the macro 100 times.
Thanks anyway

ANSWER: As long as you can define logically what is to happen, one macro can do the job - for instance

Sub CopyChart1()
dim i as integer
for i = 1 to 100
ActiveSheet.ChartObjects("Chart " & i).Activate
ActiveChart.CopyPicture _
  Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
'and do something with it

End Sub

would copy charts 1 to 100.

IF the process runs doing different things for each checkbox then

if checkbox1.value=true then
'do the checkbox1 stuff
end if
if checkbox2.value=true then
'do the checkbox2 stuff
end if
'etc etc etc

HOPEFULLY this starts to make it clearer - more than happy to help further if required.

---------- FOLLOW-UP ----------

QUESTION: Sorry this is not what I was after. I have wasted enough of your time on this.
Doing a if checkbox1.value=true 100 time is not an option either as then it is far too slow to execute. Anyway I needed to deliver a model today and a fast one, so i think my only option was to copy my macro 100 times and assign each one to each checkbox (I have used a macro for that so did not waste too much time!)
thanks again

I'm sorry I failed so dismally this time- I do my best to try to read between the lines and fill in any missing information, but in this instance I clearly failed - I'm certain there is a single, fast, one macro way, but identifying it this time has proved elusive.  Sorry.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Aidan Heritage


I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!


My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2017 All rights reserved.