Order Numbers?

[Youandih]

Good day,

I have a question: I work at a phone retail shop, and we use Excel to make the receipts for our customers.
In order to find the receipts back if there's a problem, we have to (manually!) increase a number, the order number, everytime we open the file.

So, one customer comes, buys something, we make receipt and change the order number to e.g. 000501. We save it, we print it.
Then, the next customer comes, buys something, we make receipt and change order number to 000502, save, print.

Is there a way to automate that process, so that everytime we open the blank receipt, we get a sequential, unique number?

Thanks, Youandi Hoefman

 

[BrianMH]

Are multiple people using the spreadsheet at once or are you just using it 1 person at a time? If it is only one at a time you could create a macro to clear all the cells and increase the order number by 1 after the file is saved so it is ready for the next use. Are you familiar with VBA at all?

 

[Andrew Poulsom]

Welcome to MrExcel.

You can use a macro:

http://www.mrexcel.com/forum/excel-questions/49835-re-auto-gererated-number-when-sheet-opened.html

 

[Youandih]

Are multiple people using the spreadsheet at once or are you just using it 1 person at a time? If it is only one at a time you could create a macro to clear all the cells and increase the order number by 1 after the file is saved so it is ready for the next use. Are you familiar with VBA at all?

Thanks for the response, only one person is using the receipt at a time. I am not familiar with VBA, but I know some scripting. Could you be as kind as to tell me where to start?
The order of actions must be (after filling in the cells) to save the filled receipt as e.g. "000501" (the order number) at a specific destination, then print that filled receipt, clear the cells, increase the number, and then save the empty increased number receipt as the original empty sheet which I opened before filling it in, so that next time, the number is increased.

<edit:>(EDIT: I just realized I actually am familiar with VBA, not much but I know the basics.)</edit:>

 

[Andrew Poulsom]

Did you look at the link I posted?

 

[Youandih]

Did you look at the link I posted?

Yes I've been doing that just now! Happy to say it worked!
Thank you for your cooperation :D

Just one question: is there a way to automatically save the file in a folder named "receipts\*.xls" with the "*" being the number used as the order number?

So when you filled in the receipt, and print it, it automatically calls it "1000502", and next time "1000503"?

 

[BrianMH]

thisworkbook.saveas("C:\Path\To\Folder\Reciepts" & range("A1") & ".xls")

That is the line of code that would save it. Change the cell reference as necessary.

 

[Andrew Poulsom]

Maybe this will help you:

http://www.mrexcel.com/forum/excel-questions/473320-renaming-workbook.html

 

[Youandih]

thisworkbook.saveas("C:\Path\To\Folder\Reciepts" & range("A1") & ".xls")

That is the line of code that would save it. Change the cell reference as necessary.

Is there a way to build in a function that only when I print it, it saves it using the line you mentioned?
Preferably in a separate function.

 

[BrianMH]

Add this to the ThisWorkbook Module.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ThisWorkbook.SaveAs ("C:\Path\To\Folder\Reciepts" & Range("A1") & ".xls")
End Sub