Adding an auto number

[naq_uk]

PLEASE HELP

I am doing a project for college and need some help. I have a sheet that will open on a template but need an auto number to appear in two cells (E3:E4) automatically that are unique each time, preferably sequential. Can some1 help me??????????????

[daleyman]

a little vba could fix this, but to verify the request, please confirm my understanding,

each time a sheet is added to the workbook, two new numbers are generated sequentially following the highest value of any value in these two cells from any other sheet???

try this in the workbook module...


Private Sub Workbook_NewSheet(ByVal Sh As Object)
maxval = 0
For sn = 1 To ActiveWorkbook.Sheets.Count
If Sheets(sn).Name <> Sh.Name Then
If IsNumeric(Sheets(sn).Range("e3")) And Sheets(sn).Range("e3") > maxval Then maxval = Sheets(sn).Range("e3").Value
If IsNumeric(Sheets(sn).Range("e4")) And Sheets(sn).Range("e4") > maxval Then maxval = Sheets(sn).Range("e4").Value
End If
Next
Sh.Range("e3") = maxval + 1
Sh.Range("e4") = maxval + 2
End Sub

[naq_uk]

Thanks for your help. I kind of get it but still am a bit confused. Do i replace Sheets with the name of my sheet. Sorry to inconvenience u but is it possible for you to write it again as before but include the sheet name "invoice" in the appropriate places?? THANKS. I AM VERY GRATEFUL!!!

FELLOW LONDONER!

[daleyman]

no, i intended the code for use as-is. it searchs all sheets when you open a new one, and gets two new values sequentially higher than those that appear in the same cells on any other sheet that's already existing in the workbook.

add it to the workbook module of a new book, then keep adding new sheets, you'll see what i mean...

[Ivan F Moala]

On 2002-04-13 15:29, daleyman wrote:
a little vba could fix this, but to verify the request, please confirm my understanding,

each time a sheet is added to the workbook, two new numbers are generated sequentially following the highest value of any value in these two cells from any other sheet???

try this in the workbook module...

Private Sub Workbook_NewSheet(ByVal Sh As Object)

    maxval = 0

    For sn = 1 To ActiveWorkbook.Sheets.Count

        If Sheets(sn).Name <> Sh.Name Then

            If IsNumeric(Sheets(sn).Range("e3")) And Sheets(sn).Range("e3") > maxval Then maxval = Sheets(sn).Range("e3").Value

            If IsNumeric(Sheets(sn).Range("e4")) And Sheets(sn).Range("e4") > maxval Then maxval = Sheets(sn).Range("e4").Value

        End If

    Next

    Sh.Range("e3") = maxval + 1

    Sh.Range("e4") = maxval + 2

End Sub

[nisht]

download the file no 14

http://www.pexcel.com/download.htm

here i have given the example of autonumbering.. or you can say invoicnumbering.

see if it is helpful to you.

file shows how you can use roman nos ..

prefix and suffix numbering ..

here you can even change start nos

it is better if you can see this file.

nishith desai
http://www.pexcel.com

[naq_uk]

Thanks everyone for your help. It is much appreciated!

Love
Naz

[naq_uk]

so far I have tried the following:

On 2002-04-14 07:40, daleyman wrote:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
maxval = 0
For sn = 1 To ActiveWorkbook.Sheets.Count
If Sheets(sn).Name <> Sh.Name Then
If IsNumeric(Sheets(sn).Range("e3")) And Sheets(sn).Range("e3") > maxval Then maxval = Sheets(sn).Range("e3").Value
If IsNumeric(Sheets(sn).Range("e4")) And Sheets(sn).Range("e4") > maxval Then maxval = Sheets(sn).Range("e4").Value
End If
Next
Sh.Range("e3") = maxval + 1
Sh.Range("e4") = maxval + 2
End Sub


...is all you need, take away the extra "Sub AddNewNumber"...the statement:

Private Sub Workbook_NewSheet(ByVal Sh As Object)

...opens the procedure, you need to paste the block of code into the module that relates to the Workbook. NewSheet is the event, therefore, whenever you add a new sheet this macro will run automatically, because excel will look in the Workbook module to see if there is such a procedure.

If you don't know where the Workbook module is, go into the Visual Basic Editor and double click on the "ThisWorkbook" label, this will open up the code sheet for that object.

Good Luck....
_________________

And this only worked when a new sheet was inserted and not for when a new sheet is opened from a template. So i tried the following:

On 2002-04-14 08:15, daleyman wrote:
try putting this code into the "ThisWorkbook" module of the worksheet...


Private Sub Workbook_Open()

val1 = Sheets("Sheet2").Range("E3").Value
val2 = Sheets("Sheet2").Range("E4").Value

If IsNumeric(val1) And IsNumeric(val2) Then
If val1 > val2 Then val3 = val1 Else val3 = val2
Sheets("sheet2").Range("E3").Value = val3 + 1
Sheets("sheet2").Range("E4").Value = val3 + 2
End If

End Sub


...the idea being that every time you open the workbook, it gets the values from cells E3 and E4, and if they are numeric, substitutes them for two new values sequentially higher than the maximum of the two existing values...

_________________

but this kept giving the same values in e3 and e4 every time i opened a new worksheet from the template.

What should happen is that every time a new sheet is opened from the template a different and unique number should appear in e3 and e4. Any more ideas???

(thanks to Nishith desai! but it only adds a new number going down the column each time!)

Thanks

Naz

[ This Message was edited by: naq_uk on 2002-04-15 14:41 ]