excel vba - automatic invoice numbering

C

chief

i am trying to set up something in cell K5 whereby it will produce upo
opening, a sequential number 1 higher than the last.

I think the end code is Range("K5").Value + 1

but how do i set it up to get that sequencing
 
R

Ron de Bruin

Hi

You need this event to run your code line when the workbook opens

Private Sub Workbook_Open()
With Sheets("sheet1")
.Range("K5").Value = .Range("K5").Value + 1
End With
End Sub

Right click on the Excel icon next to file on the menu bar
Choose view code
The ThisWorkbook module is active now
Paste the code in there
Save/close and reopen the file
 
C

chief

thanks for the reply, it worked very well.

another question

i have set it up so that when they click a command button i
automatically saves the invoice sheet into my documents with the K
cell contents as the file name. However, i would like to have it se
up so that one they save and close, the next time they open the shee
to do another invoice it will be 1 number higher than the last save
invoice number. The reply posted before took care of most of that, bu
now it seems that when i click the command button to auto save it to m
docs and close the sheet, when i open a new sheet it comes up as th
same number. Should there be some sort of loop code in there
 
C

chief

this is the basic run down. i open up my invoice retail sheet, ente
the customers order and then click on a command button whic
automatically saves that sheet with that customers info into m
documents, and saves the file as the contents of cell K5 (this is wher
the auto generated sequential number should be). As soon as it get
saved it is printed and the sheet closes. Then i open the invoic
retail sheet again and start from scratch. The problem is i have se
up a code upon workbook open such as:

Range("K5").Value = Range("K5").Value + 1
Numberformat = 0000

The dilemma i'm facing is everything else works but i want to obviousl
save each invoice under a new sequential number to be stored in mydocs
When i open the new sheet after saving, it doesn't jump to the nex
sequential number. Can anybody help me?

tahnks alo
 
R

Ron de Bruin

Hi chief

You must save the "invoice retail sheet" ( I think you mean workbook)
before you close it

Or are you using a sheet template?
 
Top