how to make a cell value increment every time a new worksheet is o

[mabari]

Kindly help as I need to make Invoice and need the invoice number to
increment every time I use a new invoice. Thanks and regards.
Bari

 

[Mike H.]

If you are in a multi-user environment, the way to do it is to use either a
text file or an access db to store information like this. Then you can
increment and store in that file and then read into a variable in excel.
That is how I do it. If just a single-user environment, put the number on a
hidden sheet and then unhide, read, increment and then use the number. Good
luck.

Here is code for a text file:

Sub incrementInvoiceNbr()
Dim FName As String
FName = "C:\temp\invoice.no"
FF = FreeFile()
Open FName For Input As #FF
Line Input #FF, L
MsgBox (L)

Close #FF
Kill "c:\temp\invoice.no"
FF = FreeFile()
Open "C:\TEMP\INVOICE.NO" For Output As #FF
Write #FF, Val(L) + 1

Close #FF


End Sub