want to save files with sequential names

[PJ]

Each week I want to save a new XL spreadsheet with the next number in a sequence. e.g. ABC0010, ABC0011, ABC0012 etc.

As part of the work I enter a lookup formula to test for the existence of any data (part numbers) in the previous week's sheet to identify the part numbers that are new to the current week.
As I create the sheet using a macro to manipulate and edit and sort the data and print it, it would be nice if I could automate the last step - looking up in the sheet with the highest existing number file suffix and saving the file with the next appropriate filename.

I have found it rather difficult to search/browse the bulletin board to find some ideas.
Any suggestions would be greatly appreciated.
Thanks.

 

[DonkeyOte]

Can you not enter the last week in a cell on the sheet (only need to do this once) - then say this is entered in Z1 - your current week could then say be in A1 - = z1 + 1

ActiveWorkbook.SaveAs ("C:blah" & sheets("sheet1").range("a1") & ".xls")

As part of the macro you would then take A1 and paste it as value into Z1, so the next time you run the macro A1 will increase by 1.

I use a similar process for Purchase Ordering where each purchase order number is calculated by looking into a Database to find the last number, then saving the PO itself as the nextnumber.xls