Sequential File Saving

[John]

Hi

I would like to know if it possible to have a piece of
visual basic code that when executed will perform a "save
as" with a sequential number. That is to say each time you
run the code it has for example "save as" 00001.xls,
00002.xls, 00003.xls etc etc.

Also where should I should place the code. I am running
Windows 2000, with Excel 2000.

Thanks

John

 

[Frank Kabel]

Hi John
one way (using cell A1 on sheet1 to store this number) would be the
following macro (for getting started with macros see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm)

sub save_me_seq()
dim fname
with activeworkbook.worksheets("sheet1").range("A1")
.value = .value+1
fname = format(.value,"00000") & ".xls"
end with
activeworkbook.saveas fname
end sub