want to save files with sequential names

PJ

New Member
Joined
Jun 12, 2002
Messages
5
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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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
 

Forum statistics

Threads
1,143,702
Messages
5,720,375
Members
422,281
Latest member
jantoooh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top