Im working on a new `Time Sheet` for work and need a way of generating an order number.

At the moment I have used the format PK00001, PK00002 etc in a cell and have created a list (using data - validation - list) with a pull down menu to select the number used/required.

I have then obtained a macro (through google :-) ) to save the file as follows;

PK0001, Staff Member, Customer, Date

Macro Script;

Sub savesheets()

Dim newfile As String, ssno As String, eng As String, cust As String, loc As String

ssno = Range("J2").Value
eng = Range("E6").Value
cust = Range("e4").Value
loc = Range("j4").Value

newfile = ssno & " " & eng & ", " & cust & " " & loc & " Service Report.xls"
ChDir _
"C:\Documents and Settings\KHS\Desktop\Drafts"
ActiveWorkbook.SaveAs Filename:=newfile

End Sub

While this method is ok it would be better if there was a way excel could generate the number automatically as this does eventually need to be idiot proof.

Or is there a way of having a master file, which will save the edited file as a copy (see above macro) but will not let you use the same number twice?

If anyone has any suggestions of their experience or ideas, even if its not a sollution I would be greatful.

Thanks.