Auto number on sales sheet

[Adam]

I have created a sales sheet template in excel 2003 and would like it to
generate a unique number every time it is opened. the file would then be
saved as a new name, but when you reopened the template it put the next
number in.

any help would be appreciated

 

[Kassie]

In your workbook open event, insert code to increment the number
I normally read the cell's value into a variable, then increment the number
on pasting it back to the sheet.

Something like

vNumber = Range("your nr cell").value
Range("your nr cell")= vNumber+1
ActiveWorkbook.save

--
HTH

Kassie

Replace xxx with hotmail

 

[Adam]

I tried this, but it did not work.

Any other ideas?

 

[Kassie]

Let's try again.

Press <Alt><F11>
In the left hand window, rightclick on ThisWorkbook
In the right hand window, there are two dropdown boxes. Click on the arrow
in the lefthand one, and select Workbook
A new procedure will open, called Private Sub Workbook_Open()
followed by a blank line, and then End Sub.
In the blank line insert the following:
Dim vNumber as variant and press <Enter>
Now, say you use cell F10 as the number, then enter:
vNumber= Range("F10")
Range("F10")=vNumber+1
activeworkbook.save
Below that will now appear End Sub
close the VBA window, by clicking the X on the right top.

Insert a value in F10, then save your file.
Close the file.
When you now re-open the file, the number will increment by 1

--
HTH

Kassie

Replace xxx with hotmail