Adding an auto number

naq_uk

New Member
Joined
Apr 11, 2002
Messages
8
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??????????????
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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
 
Upvote 0
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!
 
Upvote 0
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...
 
Upvote 0
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...

<pre/>
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

</pre>
 
Upvote 0
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<u>module that relates to the Workbook</u>. 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....
_________________<table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P</td></table>

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...

_________________<table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P</td></table>

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 Ni****h 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
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top