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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

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,182,127
Messages
5,933,798
Members
436,912
Latest member
David patrick

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