Looping and saving Macro

[ftsang]

Hi guys,

Is there a macro that I can use which, given a list of numbers (in a sheet called Variables.xls), and an empty report shell (Shell.xls), will:

1) Run a macro (within Shell.xls) based on the first number in Variables.xls
2) Save the output as 'number'.xls, where 'number' is the number in the list
3) Close 'number'.xls and re-open the report shell
4) Pick the next number in the list

etc, until the last number is used.

Sounds complicated, but should be easy for you guys! The main bit I am having problems with is the looping and saving.....

Thanks!

 

[SiddersAtCHEP]

I think that the problem you will have with this is that you are closing the file on which you have the macro running. You could either create a separate file to hold the macro or put the macro on the Variables file instead. Otherwise, if you were not too concerned about the macro being on all the copies, get "Number" from the Variables file and in Shell SAVE AS the new name, get the next number and SAVE AS again but this time you will be saving 1.xls (for example) as 2.xls. AT the end of this, you can close the final file.